﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;
namespace Human
{
	public class frm_dsdcdongbhxh : System.Windows.Forms.Form
    {

        #region Danh sach dieu chinh lao dong va muc dong bhxh
        private DataSet ds =new DataSet();
        private DataSet ds_nv = new DataSet();
        private DataSet ds_mau03a = new DataSet();
        private DataSet ds_kq_mau03a = new DataSet();
        private DataSet ds_ts_mau03a = new DataSet();
        private DataSet ds_dcbhxh = new DataSet();
        private DataSet ds_kq_mau03a_excel = new DataSet();
        string s_bophan="",user_dn="", tungay = "", denngay = "", ngay = "", ReportName = "", msg = "", title = "", s_mmyy = "",user="";
        AccessData mDB = new AccessData();
        Language lan = new Language();
        ExportToExcel exp = new ExportToExcel();
		DataTable dt_thang = new DataTable();
		DataTable dt_nam = new DataTable();
        DataTable dt_ngach = new DataTable();
        private CheckBox checkxem;
        private Button btnketthuc;
        private Button btnchon;
        private Label label4;
        private Label label3;
        private ComboBox cmbhoten;
        private TextBox txttongso;
        private TextBox txtmanv;
        private GroupBox groupBox1;
        private DataGridView dgvDanhSach;
        private CheckBox checkupdate;
        private CheckBox checkin;
        private CheckBox chk_trathe;
        private Button btn_excel;
        private Label label1;
        private haison haison1;
        private DataGridViewTextBoxColumn stt;
        private DataGridViewTextBoxColumn hoten;
        private DataGridViewTextBoxColumn sobhxh;
        private DataGridViewTextBoxColumn ngaysinh;
        private DataGridViewTextBoxColumn hslc_cu;
        private DataGridViewTextBoxColumn hscv_cu;
        private DataGridViewTextBoxColumn hsvk_cu;
        private DataGridViewTextBoxColumn hstn_cu;
        private DataGridViewTextBoxColumn hskv_cu;
        private DataGridViewTextBoxColumn hs_lc;
        private DataGridViewTextBoxColumn hs_cv;
        private DataGridViewTextBoxColumn hs_vk;
        private DataGridViewTextBoxColumn hs_tn;
        private DataGridViewTextBoxColumn hs_kv;
        private DataGridViewTextBoxColumn tuthang;
        private DataGridViewTextBoxColumn denthang;
        private DataGridViewTextBoxColumn tyle;
        private DataGridViewCheckBoxColumn trathe;
        private DataGridViewTextBoxColumn ghichu;
		private System.ComponentModel.Container components = null;
        #endregion

        public frm_dsdcdongbhxh(string s_makp,string s_userdn)
		{
			InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
		}
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}
        string  thang = "", nam = "";
        decimal thang1 = 0, nam1 = 0;
		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle1 = new System.Windows.Forms.DataGridViewCellStyle();
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle2 = new System.Windows.Forms.DataGridViewCellStyle();
            this.checkxem = new System.Windows.Forms.CheckBox();
            this.btnketthuc = new System.Windows.Forms.Button();
            this.btnchon = new System.Windows.Forms.Button();
            this.label4 = new System.Windows.Forms.Label();
            this.label3 = new System.Windows.Forms.Label();
            this.cmbhoten = new System.Windows.Forms.ComboBox();
            this.txttongso = new System.Windows.Forms.TextBox();
            this.txtmanv = new System.Windows.Forms.TextBox();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.dgvDanhSach = new System.Windows.Forms.DataGridView();
            this.stt = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.hoten = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.sobhxh = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.ngaysinh = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.hslc_cu = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.hscv_cu = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.hsvk_cu = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.hstn_cu = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.hskv_cu = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.hs_lc = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.hs_cv = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.hs_vk = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.hs_tn = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.hs_kv = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.tuthang = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.denthang = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.tyle = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.trathe = new System.Windows.Forms.DataGridViewCheckBoxColumn();
            this.ghichu = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.checkupdate = new System.Windows.Forms.CheckBox();
            this.checkin = new System.Windows.Forms.CheckBox();
            this.btn_excel = new System.Windows.Forms.Button();
            this.label1 = new System.Windows.Forms.Label();
            this.haison1 = new Human.haison();
            this.groupBox1.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.dgvDanhSach)).BeginInit();
            this.SuspendLayout();
            // 
            // checkxem
            // 
            this.checkxem.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
            this.checkxem.Checked = true;
            this.checkxem.CheckState = System.Windows.Forms.CheckState.Checked;
            this.checkxem.Location = new System.Drawing.Point(386, 538);
            this.checkxem.Name = "checkxem";
            this.checkxem.Size = new System.Drawing.Size(85, 17);
            this.checkxem.TabIndex = 17;
            this.checkxem.Text = "Xem trang in";
            this.checkxem.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.checkxem.UseVisualStyleBackColor = true;
            this.checkxem.CheckedChanged += new System.EventHandler(this.checkxem_CheckedChanged);
            this.checkxem.KeyDown += new System.Windows.Forms.KeyEventHandler(this.cmbquy_KeyDown);
            // 
            // btnketthuc
            // 
            this.btnketthuc.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
            this.btnketthuc.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.btnketthuc.Image = global::Human.Properties.Resources.close_r1;
            this.btnketthuc.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.btnketthuc.Location = new System.Drawing.Point(629, 533);
            this.btnketthuc.Name = "btnketthuc";
            this.btnketthuc.Size = new System.Drawing.Size(80, 28);
            this.btnketthuc.TabIndex = 19;
            this.btnketthuc.Text = "Kết thúc";
            this.btnketthuc.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.btnketthuc.UseVisualStyleBackColor = true;
            this.btnketthuc.Click += new System.EventHandler(this.btnketthuc_Click);
            this.btnketthuc.KeyDown += new System.Windows.Forms.KeyEventHandler(this.cmbquy_KeyDown);
            // 
            // btnchon
            // 
            this.btnchon.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
            this.btnchon.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.btnchon.Image = global::Human.Properties.Resources.ok;
            this.btnchon.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.btnchon.Location = new System.Drawing.Point(497, 533);
            this.btnchon.Name = "btnchon";
            this.btnchon.Size = new System.Drawing.Size(60, 28);
            this.btnchon.TabIndex = 18;
            this.btnchon.Text = "Chọn";
            this.btnchon.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.btnchon.UseVisualStyleBackColor = true;
            this.btnchon.Click += new System.EventHandler(this.btnchon_Click);
            this.btnchon.KeyDown += new System.Windows.Forms.KeyEventHandler(this.cmbquy_KeyDown);
            // 
            // label4
            // 
            this.label4.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
            this.label4.Location = new System.Drawing.Point(63, 522);
            this.label4.Name = "label4";
            this.label4.Size = new System.Drawing.Size(58, 13);
            this.label4.TabIndex = 21;
            this.label4.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // label3
            // 
            this.label3.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
            this.label3.Location = new System.Drawing.Point(8, 522);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(40, 13);
            this.label3.TabIndex = 20;
            this.label3.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // cmbhoten
            // 
            this.cmbhoten.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
            this.cmbhoten.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
            this.cmbhoten.FormattingEnabled = true;
            this.cmbhoten.Location = new System.Drawing.Point(66, 538);
            this.cmbhoten.Name = "cmbhoten";
            this.cmbhoten.Size = new System.Drawing.Size(191, 21);
            this.cmbhoten.TabIndex = 2;
            this.cmbhoten.SelectedIndexChanged += new System.EventHandler(this.cmbhoten_SelectedIndexChanged);
            this.cmbhoten.KeyDown += new System.Windows.Forms.KeyEventHandler(this.cmbquy_KeyDown);
            // 
            // txttongso
            // 
            this.txttongso.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
            this.txttongso.BackColor = System.Drawing.SystemColors.Control;
            this.txttongso.Location = new System.Drawing.Point(724, 538);
            this.txttongso.Name = "txttongso";
            this.txttongso.ReadOnly = true;
            this.txttongso.Size = new System.Drawing.Size(60, 20);
            this.txttongso.TabIndex = 26;
            this.txttongso.TextAlign = System.Windows.Forms.HorizontalAlignment.Center;
            // 
            // txtmanv
            // 
            this.txtmanv.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
            this.txtmanv.Enabled = false;
            this.txtmanv.Location = new System.Drawing.Point(11, 538);
            this.txtmanv.Name = "txtmanv";
            this.txtmanv.ReadOnly = true;
            this.txtmanv.Size = new System.Drawing.Size(51, 20);
            this.txtmanv.TabIndex = 1;
            this.txtmanv.TextAlign = System.Windows.Forms.HorizontalAlignment.Center;
            this.txtmanv.KeyDown += new System.Windows.Forms.KeyEventHandler(this.cmbquy_KeyDown);
            // 
            // groupBox1
            // 
            this.groupBox1.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
                        | System.Windows.Forms.AnchorStyles.Left)
                        | System.Windows.Forms.AnchorStyles.Right)));
            this.groupBox1.Controls.Add(this.dgvDanhSach);
            this.groupBox1.Location = new System.Drawing.Point(5, 101);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(779, 411);
            this.groupBox1.TabIndex = 24;
            this.groupBox1.TabStop = false;
            // 
            // dgvDanhSach
            // 
            this.dgvDanhSach.AllowUserToAddRows = false;
            this.dgvDanhSach.BackgroundColor = System.Drawing.SystemColors.Window;
            dataGridViewCellStyle1.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;
            dataGridViewCellStyle1.BackColor = System.Drawing.SystemColors.Control;
            dataGridViewCellStyle1.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            dataGridViewCellStyle1.ForeColor = System.Drawing.SystemColors.WindowText;
            dataGridViewCellStyle1.SelectionBackColor = System.Drawing.SystemColors.Highlight;
            dataGridViewCellStyle1.SelectionForeColor = System.Drawing.SystemColors.HighlightText;
            dataGridViewCellStyle1.WrapMode = System.Windows.Forms.DataGridViewTriState.True;
            this.dgvDanhSach.ColumnHeadersDefaultCellStyle = dataGridViewCellStyle1;
            this.dgvDanhSach.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
            this.dgvDanhSach.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {
            this.stt,
            this.hoten,
            this.sobhxh,
            this.ngaysinh,
            this.hslc_cu,
            this.hscv_cu,
            this.hsvk_cu,
            this.hstn_cu,
            this.hskv_cu,
            this.hs_lc,
            this.hs_cv,
            this.hs_vk,
            this.hs_tn,
            this.hs_kv,
            this.tuthang,
            this.denthang,
            this.tyle,
            this.trathe,
            this.ghichu});
            this.dgvDanhSach.Cursor = System.Windows.Forms.Cursors.Default;
            this.dgvDanhSach.Dock = System.Windows.Forms.DockStyle.Fill;
            this.dgvDanhSach.Location = new System.Drawing.Point(3, 16);
            this.dgvDanhSach.Name = "dgvDanhSach";
            dataGridViewCellStyle2.BackColor = System.Drawing.SystemColors.Control;
            dataGridViewCellStyle2.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            dataGridViewCellStyle2.ForeColor = System.Drawing.SystemColors.WindowText;
            dataGridViewCellStyle2.SelectionBackColor = System.Drawing.SystemColors.Highlight;
            dataGridViewCellStyle2.SelectionForeColor = System.Drawing.SystemColors.HighlightText;
            dataGridViewCellStyle2.WrapMode = System.Windows.Forms.DataGridViewTriState.True;
            this.dgvDanhSach.RowHeadersDefaultCellStyle = dataGridViewCellStyle2;
            this.dgvDanhSach.RowHeadersVisible = false;
            this.dgvDanhSach.RowHeadersWidth = 20;
            this.dgvDanhSach.Size = new System.Drawing.Size(773, 392);
            this.dgvDanhSach.TabIndex = 0;
            // 
            // stt
            // 
            this.stt.DataPropertyName = "stt";
            this.stt.HeaderText = "STT";
            this.stt.Name = "stt";
            this.stt.Width = 30;
            // 
            // hoten
            // 
            this.hoten.DataPropertyName = "hoten";
            this.hoten.HeaderText = "Họ tên";
            this.hoten.Name = "hoten";
            this.hoten.Width = 130;
            // 
            // sobhxh
            // 
            this.sobhxh.DataPropertyName = "so_bhxh";
            this.sobhxh.HeaderText = "Số BHXH";
            this.sobhxh.Name = "sobhxh";
            this.sobhxh.Width = 80;
            // 
            // ngaysinh
            // 
            this.ngaysinh.DataPropertyName = "ngaysinh";
            this.ngaysinh.HeaderText = "Ngày sinh";
            this.ngaysinh.Name = "ngaysinh";
            this.ngaysinh.Width = 80;
            // 
            // hslc_cu
            // 
            this.hslc_cu.DataPropertyName = "hslc_cu";
            this.hslc_cu.HeaderText = "Tiền lương, tiền công cũ";
            this.hslc_cu.Name = "hslc_cu";
            this.hslc_cu.Width = 40;
            // 
            // hscv_cu
            // 
            this.hscv_cu.DataPropertyName = "hscv_cu";
            this.hscv_cu.HeaderText = "Chức vụ cũ";
            this.hscv_cu.Name = "hscv_cu";
            this.hscv_cu.Width = 40;
            // 
            // hsvk_cu
            // 
            this.hsvk_cu.DataPropertyName = "hsvk_cu";
            this.hsvk_cu.HeaderText = "Vượt khung cũ";
            this.hsvk_cu.Name = "hsvk_cu";
            this.hsvk_cu.Width = 40;
            // 
            // hstn_cu
            // 
            this.hstn_cu.DataPropertyName = "hstn_cu";
            this.hstn_cu.HeaderText = "Thâm niên nghề cũ";
            this.hstn_cu.Name = "hstn_cu";
            this.hstn_cu.Width = 40;
            // 
            // hskv_cu
            // 
            this.hskv_cu.DataPropertyName = "hskv_cu";
            this.hskv_cu.HeaderText = "Khu vực cũ";
            this.hskv_cu.Name = "hskv_cu";
            this.hskv_cu.Width = 40;
            // 
            // hs_lc
            // 
            this.hs_lc.DataPropertyName = "hs_lc";
            this.hs_lc.HeaderText = "Tiền lương, tiền công";
            this.hs_lc.Name = "hs_lc";
            this.hs_lc.Width = 40;
            // 
            // hs_cv
            // 
            this.hs_cv.DataPropertyName = "hs_cv";
            this.hs_cv.HeaderText = "Chức vụ";
            this.hs_cv.Name = "hs_cv";
            this.hs_cv.Width = 40;
            // 
            // hs_vk
            // 
            this.hs_vk.DataPropertyName = "hs_vk";
            this.hs_vk.HeaderText = "Vượt khung";
            this.hs_vk.Name = "hs_vk";
            this.hs_vk.Width = 40;
            // 
            // hs_tn
            // 
            this.hs_tn.DataPropertyName = "hs_tn";
            this.hs_tn.HeaderText = "Thâm niên nghề";
            this.hs_tn.Name = "hs_tn";
            this.hs_tn.Width = 40;
            // 
            // hs_kv
            // 
            this.hs_kv.DataPropertyName = "hs_kv";
            this.hs_kv.HeaderText = "Khu vực";
            this.hs_kv.Name = "hs_kv";
            this.hs_kv.Width = 40;
            // 
            // tuthang
            // 
            this.tuthang.DataPropertyName = "thangnam_bhxh";
            this.tuthang.HeaderText = "Từ tháng";
            this.tuthang.Name = "tuthang";
            this.tuthang.Width = 60;
            // 
            // denthang
            // 
            this.denthang.DataPropertyName = "dong_den_thang_nam";
            this.denthang.HeaderText = "Đến tháng";
            this.denthang.Name = "denthang";
            this.denthang.Width = 60;
            // 
            // tyle
            // 
            this.tyle.DataPropertyName = "tyle_bosung";
            this.tyle.HeaderText = "Tỷ lệ bổ sung";
            this.tyle.Name = "tyle";
            this.tyle.Width = 40;
            // 
            // trathe
            // 
            this.trathe.DataPropertyName = "trathe";
            this.trathe.HeaderText = "Trả thẻ";
            this.trathe.Name = "trathe";
            this.trathe.Resizable = System.Windows.Forms.DataGridViewTriState.True;
            this.trathe.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.Automatic;
            this.trathe.Width = 40;
            // 
            // ghichu
            // 
            this.ghichu.DataPropertyName = "ghichu";
            this.ghichu.HeaderText = "Ghi chú";
            this.ghichu.Name = "ghichu";
            // 
            // checkupdate
            // 
            this.checkupdate.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
            this.checkupdate.Font = new System.Drawing.Font("Microsoft Sans Serif", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.checkupdate.Location = new System.Drawing.Point(269, 538);
            this.checkupdate.Name = "checkupdate";
            this.checkupdate.Size = new System.Drawing.Size(70, 19);
            this.checkupdate.TabIndex = 17;
            this.checkupdate.Text = "Update";
            this.checkupdate.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.checkupdate.UseVisualStyleBackColor = true;
            this.checkupdate.Visible = false;
            this.checkupdate.KeyDown += new System.Windows.Forms.KeyEventHandler(this.cmbquy_KeyDown);
            // 
            // checkin
            // 
            this.checkin.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
            this.checkin.Location = new System.Drawing.Point(345, 540);
            this.checkin.Name = "checkin";
            this.checkin.Size = new System.Drawing.Size(35, 17);
            this.checkin.TabIndex = 17;
            this.checkin.Text = "In";
            this.checkin.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.checkin.UseVisualStyleBackColor = true;
            this.checkin.CheckedChanged += new System.EventHandler(this.checkin_CheckedChanged);
            this.checkin.KeyDown += new System.Windows.Forms.KeyEventHandler(this.cmbquy_KeyDown);
            // 
            // btn_excel
            // 
            this.btn_excel.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
            this.btn_excel.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.btn_excel.Image = global::Human.Properties.Resources.white_excel;
            this.btn_excel.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.btn_excel.Location = new System.Drawing.Point(563, 533);
            this.btn_excel.Name = "btn_excel";
            this.btn_excel.Size = new System.Drawing.Size(60, 28);
            this.btn_excel.TabIndex = 19;
            this.btn_excel.Text = "Excel";
            this.btn_excel.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.btn_excel.UseVisualStyleBackColor = true;
            this.btn_excel.Click += new System.EventHandler(this.btn_excel_Click);
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.label1.Location = new System.Drawing.Point(40, 50);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(416, 20);
            this.label1.TabIndex = 29;
            this.label1.Text = "DANH SÁCH ĐIỀU CHỈNH MỨC ĐÓNG BẢO HIỂM";
            // 
            // haison1
            // 
            this.haison1.Location = new System.Drawing.Point(484, 30);
            this.haison1.Name = "haison1";
            this.haison1.Size = new System.Drawing.Size(274, 68);
            this.haison1.TabIndex = 30;
            this.haison1.Validated += new System.EventHandler(this.haison1_Validated);
            this.haison1.MouseEnter += new System.EventHandler(this.haison1_MouseEnter);
            // 
            // frm_dsdcdongbhxh
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(788, 568);
            this.ControlBox = false;
            this.Controls.Add(this.haison1);
            this.Controls.Add(this.label1);
            this.Controls.Add(this.checkupdate);
            this.Controls.Add(this.checkin);
            this.Controls.Add(this.checkxem);
            this.Controls.Add(this.btn_excel);
            this.Controls.Add(this.btnketthuc);
            this.Controls.Add(this.btnchon);
            this.Controls.Add(this.label4);
            this.Controls.Add(this.label3);
            this.Controls.Add(this.cmbhoten);
            this.Controls.Add(this.txttongso);
            this.Controls.Add(this.txtmanv);
            this.Controls.Add(this.groupBox1);
            this.ForeColor = System.Drawing.Color.DarkBlue;
            this.Name = "frm_dsdcdongbhxh";
            this.ShowIcon = false;
            this.ShowInTaskbar = false;
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Điều chỉnh mức đóng BHXH";
            this.Load += new System.EventHandler(this.frm_dsdcdongbhxh_Load);
            this.groupBox1.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.dgvDanhSach)).EndInit();
            this.ResumeLayout(false);
            this.PerformLayout();

		}
		#endregion

		private void butThoat_Click(object sender, System.EventArgs e)
		{
			this.Close();
		}
        private void frm_dsdcdongbhxh_Load(object sender, EventArgs e)
        {
            haison1.Focus();
            this.WindowState = FormWindowState.Maximized;
            user = mDB.user;
        }
        //
        private DataSet tao_dataset()
        {
            DataTable dt = new DataTable("Table0");
            DataTable dt_ts = new DataTable("Table1");
            dt.Columns.Add("stt", typeof(int));
            dt.Columns.Add("manv", typeof(string));
            dt.Columns.Add("hoten", typeof(string));
            dt.Columns.Add("so_bhxh", typeof(string));
            dt.Columns.Add("ngaysinh", typeof(string));
            //phu cap muc cu
            dt.Columns.Add("hslc_cu", typeof(string));
            dt.Columns.Add("hscv_cu", typeof(decimal));
            dt.Columns.Add("hsvk_cu", typeof(decimal));
            dt.Columns.Add("hstn_cu", typeof(decimal));
            dt.Columns.Add("hskv_cu", typeof(decimal));
            //phu cap muc moi
            dt.Columns.Add("hs_lc", typeof(decimal));
            dt.Columns.Add("hs_cv", typeof(decimal));
            dt.Columns.Add("hs_vk", typeof(decimal));
            dt.Columns.Add("hs_tn", typeof(decimal));
            dt.Columns.Add("hs_kv", typeof(decimal));
            //
            dt.Columns.Add("thang_bhxh", typeof(string));
            dt.Columns.Add("nam_bhxh", typeof(string));
            dt.Columns.Add("thangnam_bhxh", typeof(string));
            dt.Columns.Add("dong_den_thang_nam", typeof(string));
            dt.Columns.Add("den_thang", typeof(string));
            dt.Columns.Add("den_nam", typeof(string));
            dt.Columns.Add("tyle_bosung", typeof(string));
            dt.Columns.Add("trathe", typeof(string)); // tra the bhyt
            dt.Columns.Add("ghichu", typeof(string));
            //
            dt.Columns.Add("sothang", typeof(string));
            dt.Columns.Add("mltt", typeof(decimal));
            //
            dt.Columns.Add("sbhxh_hs", typeof(decimal));//muc dong ty le bhxh cua nha su dung lao dong
            dt.Columns.Add("sbhyt_hs", typeof(decimal));//muc dong ty le bhyt cua nha su dung lao dong
            dt.Columns.Add("sbhtn_hs", typeof(decimal));//muc dong ty le bhtn cua nha su dung lao dong
            dt.Columns.Add("mbhxh_hs", typeof(decimal));// muc dong ty le bhxh cua nguoi lao dong
            dt.Columns.Add("mbhyt_hs", typeof(decimal));// muc dong ty le bhyt cua nguoi lao dong
            dt.Columns.Add("mbhtn_hs", typeof(decimal));// muc dong ty le bhtn cua nguoi lao dong
            dt.Columns.Add("bhxh", typeof(int));
            dt.Columns.Add("bhyt", typeof(int));
            dt.Columns.Add("bhtn", typeof(int));
            //
            dt.Columns.Add("thangnam_bacluong", typeof(string));// thang nam thay doi bac luong,he so luong
            dt.Columns.Add("thangnam_hesobh", typeof(string)); // thang nam thay doi he so bao hiem
            dt.Columns.Add("thangnam_nangbac", typeof(string));
            dt.Columns.Add("loaiphucap", typeof(string));// loai phu cap
            dt.Columns.Add("ngaynghi", typeof(string)); // ngay nghi lam 
            
            //
            dt.Columns.Add("sld_bhxh_bhyt_tang", typeof(decimal));
            dt.Columns.Add("sld_bhxh_bhyt_giam", typeof(decimal));
            dt.Columns.Add("sld_bhtn_tang", typeof(decimal));
            dt.Columns.Add("sld_bhtn_giam", typeof(decimal));
            //
            dt.Columns.Add("ps_bhxh_tang", typeof(decimal));
            dt.Columns.Add("ps_bhxh_giam", typeof(decimal));
            dt.Columns.Add("ps_bhyt_tang", typeof(decimal));
            dt.Columns.Add("ps_bhyt_giam", typeof(decimal));
            dt.Columns.Add("ps_bhtn_tang", typeof(decimal));
            dt.Columns.Add("ps_bhtn_giam", typeof(decimal));
            //
            dt.Columns.Add("bs_bhxh_tang", typeof(decimal));
            dt.Columns.Add("bs_bhxh_giam", typeof(decimal));
            dt.Columns.Add("bs_bhyt_tang", typeof(decimal));
            dt.Columns.Add("bs_bhyt_giam", typeof(decimal));
            dt.Columns.Add("bs_bhtn_tang", typeof(decimal));
            dt.Columns.Add("bs_bhtn_giam", typeof(decimal));
            //
            dt_ts.Columns.Add("ts_ld_bhxh_tang", typeof(int));
            dt_ts.Columns.Add("ts_ld_bhxh_giam", typeof(int));
            dt_ts.Columns.Add("ts_ld_bhyt_tang", typeof(int));
            dt_ts.Columns.Add("ts_ld_bhyt_giam", typeof(int));
            dt_ts.Columns.Add("ts_ld_bhtn_tang", typeof(int));
            dt_ts.Columns.Add("ts_ld_bhtn_giam", typeof(int));
            //
            dt_ts.Columns.Add("quyluong_bhxh_tang", typeof(decimal));
            dt_ts.Columns.Add("quyluong_bhxh_giam", typeof(decimal));
            dt_ts.Columns.Add("quyluong_bhyt_tang", typeof(decimal));
            dt_ts.Columns.Add("quyluong_bhyt_giam", typeof(decimal));
            dt_ts.Columns.Add("quyluong_bhtn_tang", typeof(decimal));
            dt_ts.Columns.Add("quyluong_bhtn_giam", typeof(decimal));
            //
            dt_ts.Columns.Add("phaidong_bhxh_tang", typeof(decimal));
            dt_ts.Columns.Add("phaidong_bhxh_giam", typeof(decimal));
            dt_ts.Columns.Add("phaidong_bhyt_tang", typeof(decimal));
            dt_ts.Columns.Add("phaidong_bhyt_giam", typeof(decimal));
            dt_ts.Columns.Add("phaidong_bhtn_tang", typeof(decimal));
            dt_ts.Columns.Add("phaidong_bhtn_giam", typeof(decimal));
            //
            dt_ts.Columns.Add("dieuchinh_bhxh_tang", typeof(decimal));
            dt_ts.Columns.Add("dieuchinh_bhxh_giam", typeof(decimal));
            dt_ts.Columns.Add("dieuchinh_bhyt_tang", typeof(decimal));
            dt_ts.Columns.Add("dieuchinh_bhyt_giam", typeof(decimal));
            dt_ts.Columns.Add("dieuchinh_bhtn_tang", typeof(decimal));
            dt_ts.Columns.Add("dieuchinh_bhtn_giam", typeof(decimal));
            //
            dt_ts.Columns.Add("tong_ps_bhxh_tang", typeof(decimal));
            dt_ts.Columns.Add("tong_ps_bhxh_giam", typeof(decimal));
            dt_ts.Columns.Add("tong_ps_bhyt_tang", typeof(decimal));
            dt_ts.Columns.Add("tong_ps_bhyt_giam", typeof(decimal));
            dt_ts.Columns.Add("tong_ps_bhtn_tang", typeof(decimal));
            dt_ts.Columns.Add("tong_ps_bhtn_giam", typeof(decimal));
            //
            dt_ts.Columns.Add("tong_bs_bhxh_tang", typeof(decimal));
            dt_ts.Columns.Add("tong_bs_bhxh_giam", typeof(decimal));
            dt_ts.Columns.Add("tong_bs_bhyt_tang", typeof(decimal));
            dt_ts.Columns.Add("tong_bs_bhyt_giam", typeof(decimal));
            dt_ts.Columns.Add("tong_bs_bhtn_tang", typeof(decimal));
            dt_ts.Columns.Add("tong_bs_bhtn_giam", typeof(decimal));

            DataSet ds_tmp = new DataSet();
            ds_tmp.Tables.Add(dt);
            ds_tmp.Tables.Add(dt_ts);
            return ds_tmp;
        }
        #region ty le bo sung
        /*private DataSet get_tyle_bosung(string mmyy)
        {
            string sql = "";
            sql ="  select 0 as stt,l.manv,trim(l.ho)||' '||trim(l.ten) as hoten,split_part(bl.bacluongcu,' | ',2) as heso_luongcu,hd.hs_lc as heso_luongmoi,hs.bhxh,hs.bhyt,hs.bhtn ";
            sql +=" ,he.sbhxh_hs,he.sbhyt_hs,he.sbhtn_hs,he.mbhxh_hs,he.mbhyt_hs,he.mbhtn_hs ";
            sql +=" ,case ";
            // Dieu chinh do tang giam luong (muc luong cu <> 0 va muc luong moi <> 0) co tham gia : bhxh,bhyt,bhtn (28.5 %)
	        sql +=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";
            sql +=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";
            // Dieu chinh do tang giam luong (muc luong cu <> 0 va muc luong moi <> 0) co tham gia : bhxh,bhyt (26.5 %)
	        sql +=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )  ";
	        sql +=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";
            // Giam tham gia ( ke ca giam nguyen luong): muc luong cu <> 0 va muc luong moi = 0 va co tra the BHYT kip thoi ,co tham gia BHXH,BHTN (24%)
	        sql +=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
	        sql +=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  ";
            // Giam tham gia ( ke ca giam nguyen luong): muc luong cu <> 0 va muc luong moi = 0 va co tra the BHYT kip thoi ,co tham gia BHXH (22%)
	        sql +=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )  ";
	        sql +=" then (he.sbhxh_hs+he.mbhxh_hs) ";
            // Bo sung tang /giam BHYT : tra the BHYT khong kip thoi,khong tra the BHYT (4.5%)
	        sql +=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  ";
	        sql +=" then (he.sbhyt_hs+he.mbhyt_hs) ";
            // Bo sung tang nguyen luong :muc luong cu =0 va muc luong moi <>0 va co tham gia BHXH,BHYT,BHTN (28.5%)
	        sql +=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
	        sql +=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";
            // Bo sung tang nguyen luong :muc luong cu =0 va muc luong moi <>0 va co tham gia BHXH,BHYT (26.5%)
	        sql +=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  ";
	        sql +=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";
            // Tang tham gia that nghiep :muc luong cu=0 va muc luong moi <>0 va co tham gia BHTN (2%)
	        sql +=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";
	        sql +=" then (he.sbhtn_hs+he.mbhtn_hs)  ";
            sql +=" else 0 end as tyle_bosung ";
            sql += " from " + user + ".llnv l  ";
            sql += " left join " + user + ".hopdong hd on l.manv=hd.manv ";
            sql += " left join " + user + ".thebhyt yt on l.manv=yt.manv  ";
            sql += " left join " + user + ".btdtt tt on l.t_diachi=tt.matt  ";
            sql += " left join " + user + ".btdquan qu on l.h_diachi=qu.maqu  ";
            sql += " left join " + user + ".benhvien bv on bv.mabv=yt.tai_bvien  ";
            sql += " left join " + user + ".thaydoi_bluong bl on hd.manv=bl.manv  ";
            sql += " left join " + user + ".thaydoi_phucap pc on pc.manv=l.manv ";
            sql += " left join " + user + ".heso_nv_" + s_mmyy + " hs on l.manv=hs.manv ";
            sql += " inner join " + user + ".heso he on hs.mmyy=he.mmyy ";
            sql += " inner join " + user + ".max_lan ml on hd.manv=ml.manv ";
            sql += " where ( pc.loaiphucap='vk' or pc.loaiphucap='cv' or pc.loaiphucap='tn' or pc.loaiphucap='kv') ";
            sql += " and (hd.ngaynghi is null or to_timestamp(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy') >= to_timestamp('" + denngay + "','dd/mm/yyyy') ) ";
            sql += " and to_timestamp(to_char(hd.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy') < to_timestamp('" + denngay + "','dd/mm/yyyy')  ";
            sql += " and hd.lan=ml.lan and (to_timestamp (to_char(bl.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy') between to_timestamp ('" + tungay + "','dd/mm/yyyy') ";
            sql += " and to_timestamp('" + denngay + "','dd/mm/yyyy')) ";
            return ds_tyle_bs = mDB.get_data(sql);
        }*/
#endregion
		private DataSet get_mau03a_bhxh_bhyt(string mmyy)
        {
            #region cau lenh tinh bhxh,bhyt
            string sql = "";
            sql =" select 0 as stt,l.manv,trim(l.ho)||' '||trim(l.ten) as hoten,hd.sobhxh as so_bhxh,to_char(l.ngaysinh,'dd/mm/yyyy') as ngaysinh ,split_part(bl.bacluongcu,' | ',2) as hslc_cu ";
            sql += " ,case when( pc.loaiphucap='cv') then pc.hs_cu  else 0 end as hscv_cu  ,case when ( pc.loaiphucap='vk') then pc.hs_cu else 0 end as hsvk_cu,case when ( pc.loaiphucap='tn') then pc.hs_cu else 0 end as hstn_cu,case when ( pc.loaiphucap='kv')then pc.hs_cu else 0 end as hskv_cu  ";
            sql += " ,hd.hs_lc,case when ( pc.loaiphucap='cv') then pc.hs_moi else 0 end as hs_cv,case when ( pc.loaiphucap='vk') then pc.hs_moi else 0 end as hs_vk,case when ( pc.loaiphucap='tn') then pc.hs_moi else 0 end as hs_tn ";
            sql += " ,case when ( pc.loaiphucap='kv') then pc.hs_moi else 0 end as hs_kv,he.sbhxh_hs,he.sbhyt_hs,he.sbhtn_hs,he.mbhxh_hs,he.mbhyt_hs,he.mbhtn_hs,hs.bhxh,hs.bhyt,hs.bhtn,he.mltt,bl.mmyy as thangnam_bacluong,he.mmyy as thangnam_hesobh ";
            sql +=" ,to_char(bl.ngaynangbac,'mm/yy') as thangnam_nangbac,pc.loaiphucap,hd.ngaynghi  ";
            sql +=" ,case when hd.ngaynghi is not null then true else false end as trathe ,'Nâng luong' as ghichu,to_char(hd.ngaybhxh,'mm') as thang_bhxh ";
            sql +=" ,to_char(hd.ngaybhxh,'yyyy') as nam_bhxh,to_char(hd.ngaybhxh,'mm/yy') as thangnam_bhxh,hs.mmyy as dong_den_thang_nam ";
            sql +=" ,to_char(to_timestamp('" + denngay + "','dd/mm/yyyy'),'mm') as den_thang,to_char(to_timestamp('" + denngay + "','dd/mm/yyyy'),'yyyy') as den_nam  ";
            // tinh ty le bo sung
            // Dieu chinh do tang giam luong (muc luong cu <> 0 va muc luong moi <> 0) co tham gia : bhxh,bhyt,bhtn (28.5 %)
	        sql +=" ,case when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";
            sql +=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";
            // Dieu chinh do tang giam luong (muc luong cu <> 0 va muc luong moi <> 0) co tham gia : bhxh,bhyt (26.5 %)
	        sql +=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )  ";
	        sql +=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";
            // Giam tham gia ( ke ca giam nguyen luong): muc luong cu <> 0 va muc luong moi = 0 va co tra the BHYT kip thoi ,co tham gia BHXH,BHTN (24%)
	        sql +=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
	        sql +=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  ";
            // Giam tham gia ( ke ca giam nguyen luong): muc luong cu <> 0 va muc luong moi = 0 va co tra the BHYT kip thoi ,co tham gia BHXH (22%)
	        sql +=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )  ";
	        sql +=" then (he.sbhxh_hs+he.mbhxh_hs) ";
            // Bo sung tang /giam BHYT : tra the BHYT khong kip thoi,khong tra the BHYT (4.5%)
	        sql +=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  ";
	        sql +=" then (he.sbhyt_hs+he.mbhyt_hs) ";
            // Bo sung tang nguyen luong :muc luong cu =0 va muc luong moi <>0 va co tham gia BHXH,BHYT,BHTN (28.5%)
	        sql +=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
	        sql +=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";
            // Bo sung tang nguyen luong :muc luong cu =0 va muc luong moi <>0 va co tham gia BHXH,BHYT (26.5%)
	        sql +=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  ";
	        sql +=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";
            // Tang tham gia that nghiep :muc luong cu=0 va muc luong moi <>0 va co tham gia BHTN (2%)
	        sql +=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";
	        sql +=" then (he.sbhtn_hs+he.mbhtn_hs)  ";
            sql +=" else 0 end as tyle_bosung ";
            //
            sql+=" ,case when ((hd.hs_lc >0 and split_part(bl.bacluongcu,' | ',2) =0 and (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )    then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )   then (he.sbhxh_hs+he.mbhxh_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)   then (he.sbhyt_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1)  then (he.sbhtn_hs+he.mbhtn_hs)  else 0 end) > (he.sbhxh_hs + he.mbhxh_hs) ) "; 
            sql+=" and ( hs.mmyy is not null or hs.mmyy= to_char(to_timestamp('" + denngay + "','dd/mm/yyyy'),'mm/yy'))) then 1 else 0 end as sld_bhxh_bhyt_tang ";

            sql+=" ,case when ((hd.hs_lc =0 and split_part(bl.bacluongcu,' | ',2) >0 and (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  then (he.sbhyt_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1)   then (he.sbhtn_hs+he.mbhtn_hs) else 0 end) > (he.sbhxh_hs + he.mbhxh_hs) )  ";
            sql+=" and ( hs.mmyy is not null or hs.mmyy= to_char(to_timestamp('" + denngay + "','dd/mm/yyyy'),'mm/yy'))) then 1 else 0 end as sld_bhxh_bhyt_giam ";

            sql+=" ,case when ( (hd.hs_lc>0 and split_part(bl.bacluongcu,' | ',2) =0 and ( (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)   then (he.sbhyt_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs)  else 0 end) =(he.sbhxh_hs + he.mbhxh_hs+he.sbhtn_hs+he.mbhtn_hs)  ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  ";
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs)  ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) then (he.sbhyt_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs)  "; 
            sql+=" else 0 end)=(he.sbhxh_hs + he.mbhxh_hs+he.sbhyt_hs+he.mbhyt_hs+he.sbhtn_hs+he.mbhtn_hs) ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  ";
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  then (he.sbhyt_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs)  else 0 end)=(he.sbhtn_hs+he.mbhtn_hs))  ";
            sql+=" and ( hs.mmyy is not null or hs.mmyy=to_char(to_timestamp('" + denngay + "','dd/mm/yyyy'),'mm/yy')))) then 1 else 0 end as sld_bhtn_tang ";

            sql+=" ,case when ( (hd.hs_lc=0 and split_part(bl.bacluongcu,' | ',2) >0 and ( (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1  ";
            sql+=" and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  ";
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) then (he.sbhyt_hs+he.mbhyt_hs) ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs)  "; 
            sql+=" else 0 end) =(he.sbhxh_hs + he.mbhxh_hs+he.sbhtn_hs+he.mbhtn_hs)  ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  "; 
            sql+=" and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )   then (he.sbhxh_hs+he.mbhxh_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)   then (he.sbhyt_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1)  "; 
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs) else 0 end)=(he.sbhxh_hs + he.mbhxh_hs+he.sbhyt_hs+he.mbhyt_hs+he.sbhtn_hs+he.mbhtn_hs) ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  ";
            sql+=" and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  ";
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )  then (he.sbhxh_hs+he.mbhxh_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)   then (he.sbhyt_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs) else 0 end)=(he.sbhtn_hs+he.mbhtn_hs)) "; 
            sql+=" and ( hs.mmyy is not null or hs.mmyy=to_char(to_timestamp('" + denngay + "','dd/mm/yyyy'),'mm/yy')))) then 1 else 0 end as sld_bhtn_giam  ";

            sql+=" ,case when ( (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  "; 
            sql+=" and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )  ";  
            sql+=" then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  ";  
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhtn_hs+he.mbhtn_hs) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  "; 
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 ";
            sql+=" and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)  else 0 end)=(he.sbhyt_hs+he.mbhyt_hs) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.mbhxh_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  ";
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)  else 0 end)=(he.sbhxh_hs + he.mbhxh_hs) ) then 0 else ( case when ( hs.mmyy is not null or hs.mmyy= to_char(to_timestamp('" + denngay + "','dd/mm/yyyy'),'mm/yy') ) ";
            sql+=" then  case when (((hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn) - (to_number( split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') "; 
            sql+=" then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end)  + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end ))) > 0 ) "; 
            sql+=" then  ((hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn)  - ( to_number(split_part(bl.bacluongcu,' | ',2))  + ( case when( pc.loaiphucap='cv') then pc.hs_cu  else 0 end) ";
            sql+=" + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end)  + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end ))) else 0 end else 0 end ) end as ps_bhxh_tang ";

            sql+=" ,case when ( (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null "; 
            sql+=" and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)   then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhtn_hs+he.mbhtn_hs) ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)   then (he.sbhyt_hs+he.mbhyt_hs)  ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1)  "; 
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhyt_hs+he.mbhyt_hs) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  "; 
            sql+=" and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )   then (he.sbhxh_hs+he.mbhxh_hs)  ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)   then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1)   then (he.sbhtn_hs+he.mbhtn_hs) ";  
            sql+=" else 0 end)=(he.sbhxh_hs + he.mbhxh_hs) ) then 0  else ( case when ( hs.mmyy is not null or hs.mmyy= to_char(to_timestamp('" + denngay + "','dd/mm/yyyy'),'mm/yy') ) ";
            sql+=" then   case when (((hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn) - ( to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') then pc.hs_cu  else 0 end)  ";
            sql+=" + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end)  + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end ))) > 0 )  then  (-(hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn) "; 
            sql+=" + ( to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') then pc.hs_cu  else 0 end)  + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end)  ";
            sql+=" + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end ))) else 0 end else 0 end ) end as ps_bhxh_giam  ";

            sql+=" ,case when ( (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  ";
            sql+=" and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 )   then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) ";  
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 ";
            sql+=" and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhtn_hs+he.mbhtn_hs) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) ";
            sql+=" is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) ";
            sql+=" is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) ";
            sql+=" is not null and hs.bhxh=1 and hs.bhyt=1 )   then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 ";
            sql+=" and hs.bhtn=1)  then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhyt_hs+he.mbhyt_hs) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) ";  
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 ";
            sql+=" and hs.bhtn=0 )    then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhxh_hs + he.mbhxh_hs) )  then 0 else ( case when ( hs.mmyy is not null ";
            sql+=" or hs.mmyy= to_char(to_timestamp('" + denngay + "','dd/mm/yyyy'),'mm/yy') ) then  case when (((hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn+hd.hs_kv) ";
            sql+=" - ( to_number(split_part(bl.bacluongcu,' | ',2))+ ( case when( pc.loaiphucap='cv')  then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end ) "; 
            sql+=" + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end ) +( case when( pc.loaiphucap='kv') then pc.hs_cu  else 0 end ) )) > 0 ) ";  
            sql+=" then  ((hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn+ hd.hs_kv) - ( to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') "; 
            sql+=" then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end)  + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end ) ";
            sql+=" + ( case when( pc.loaiphucap='kv') then pc.hs_cu  else 0 end ) ))  else 0 end else 0 end ) end as ps_bhyt_tang ";

            sql+=" ,case when ( (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  ";
            sql+=" and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 )   then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) "; 
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )    then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhtn_hs+he.mbhtn_hs) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )  then (he.sbhxh_hs+he.mbhxh_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  then (he.sbhyt_hs+he.mbhyt_hs) ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 ";
            sql+=" and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) "; 
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhyt_hs+he.mbhyt_hs) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) ";
            sql+=" is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) ";
            sql+=" is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) ";
            sql+=" is not null and hs.bhxh=1 and hs.bhyt=1 )  then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 ";
            sql+=" and hs.bhtn=1)    then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";   
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )    then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) "; 
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhxh_hs + he.mbhxh_hs) )  then 0 else ( case when ( hs.mmyy is not null or hs.mmyy= to_char(to_timestamp('" + denngay + "','dd/mm/yyyy'),";
            sql+=" 'mm/yy') ) then  case when (((hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn+hd.hs_kv) - ( to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') ";  
            sql+=" then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end )  + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end ) ";
            sql+=" +( case when( pc.loaiphucap='kv') then pc.hs_cu  else 0 end ) )) < 0 )   then  (-(hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn+ hd.hs_kv) + ( to_number(split_part(bl.bacluongcu,' | ',2)) ";
            sql+=" + ( case when( pc.loaiphucap='cv')   then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end)  + ( case when( pc.loaiphucap='tn') ";
            sql+=" then pc.hs_cu  else 0 end )+ ( case when( pc.loaiphucap='kv') then pc.hs_cu  else 0 end ) )) else 0 end else 0 end ) end as ps_bhyt_giam ";

            sql+=" ,case when ( (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null "; 
            sql+=" and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 )   then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) ";  
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 ";
            sql+=" and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhyt_hs+he.mbhyt_hs) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.mbhxh_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) "; 
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 ";
            sql+=" and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1)  ";
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhxh_hs + he.mbhxh_hs) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and ";
            sql+=" split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )   then (he.sbhxh_hs+he.mbhxh_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  then (he.sbhyt_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 ";
            sql+=" and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) "; 
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhtn_hs+he.mbhtn_hs)  or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) ";
            sql+=" is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) ";
            sql+=" is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) ";
            sql+=" is not null and hs.bhxh=1 and hs.bhyt=1 )  then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1";
            sql+=" and hs.bhtn=1)  then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 ";
            sql+=" and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) "; 
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs) else 0 end)=(he.sbhxh_hs + he.mbhxh_hs + he.sbhyt_hs+he.mbhyt_hs ) ) then 0  else ( case when ( hs.mmyy is not null ";
            sql+=" or hs.mmyy= to_char(to_timestamp('" + denngay + "','dd/mm/yyyy'),'mm/yy') ) then  case when (((hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn) - ( to_number(split_part(bl.bacluongcu,' | ',2)) ";
            sql+=" + ( case when( pc.loaiphucap='cv')  then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end)   + ( case when( pc.loaiphucap='tn') then pc.hs_cu  ";
            sql+=" else 0 end ))) > 0 )   then  ((hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn) - ( to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') ";  
            sql+=" then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk')   then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end )))  else 0 end else 0 end ) ";
            sql+=" end as ps_bhtn_tang ";

            sql+=" ,case when ( (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )   then (he.sbhxh_hs+he.mbhxh_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)   then (he.sbhyt_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null ";
            sql+=" and hs.bhtn=1)   then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhyt_hs+he.mbhyt_hs) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) then (he.sbhyt_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhxh_hs + he.mbhxh_hs) ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs) ";
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  then (he.sbhyt_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs) else 0 end)=(he.sbhtn_hs+he.mbhtn_hs)  ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  ";
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) then (he.sbhyt_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1)  ";
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhxh_hs + he.mbhxh_hs + he.sbhyt_hs+he.mbhyt_hs ) ) then 0  else ( case when ( hs.mmyy is not null ";
            sql+=" or hs.mmyy= to_char(to_timestamp('" + denngay + "','dd/mm/yyyy'),'mm/yy')) then  case when (((hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn) - ( to_number(split_part(bl.bacluongcu,' | ',2)) ";
            sql+=" + ( case when( pc.loaiphucap='cv')  then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end)  + ( case when( pc.loaiphucap='tn') ";
            sql+=" then pc.hs_cu  else 0 end ))) < 0 )   then  (-(hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn) + ( to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') "; 
            sql+=" then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='tn')  then pc.hs_cu  else 0 end ))) else 0 end else 0 end ) ";
            sql+=" end as ps_bhtn_giam ";
            // tinh so thang
            sql+=" ,date_part('year', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy')))*12 ";
            sql+=" + date_part('month', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy'))) as sothang ";

            sql+=" ,(case when ((hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn)- ( to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv')  then pc.hs_cu  else 0 end) ";
            sql+=" + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end)  + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end ))) >0  then ((hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn) "; 
            sql+=" -( to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv')  then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end )) ) ";
            sql+=" else 0 end )* (case when (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  ";
            sql+=" and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 )   then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  "; 
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)  else 0 end)=( he.sbhxh_hs+he.mbhxh_hs) then ( he.sbhxh_hs+he.mbhxh_hs) ";
            sql+=" else (case when ( (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  ";
            sql+=" and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 )  then (he.sbhxh_hs+he.mbhxh_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) ";  
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and ";
            sql+=" hs.bhyt=1 and hs.bhtn=0 )    then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1)  ";
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=( he.sbhxh_hs+he.mbhxh_hs +he.sbhyt_hs+he.mbhyt_hs+he.sbhtn_hs+he.mbhtn_hs )  or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) ";
            sql+=" is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null ";
            sql+=" and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )  then (he.sbhxh_hs+he.mbhxh_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)   then (he.sbhyt_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=( he.sbhxh_hs+he.mbhxh_hs +he.sbhyt_hs+he.mbhyt_hs ) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  "; 
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   ";
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=( he.sbhtn_hs+he.mbhtn_hs) )  then ( he.sbhtn_hs+he.mbhtn_hs) else 0 end ) * (date_part('year', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy')))*12 ";
            sql+=" + date_part('month', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy')))) end) * he.mltt  as bs_bhxh_tang ";
             
            sql+=" ,(case when (-(hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn)+ ( to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') ";  
            sql+=" then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end)   + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end ))) >0 ";
            sql+=" then (-(hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn)  +( to_number(split_part(bl.bacluongcu,' | ',2)) +   ( case when( pc.loaiphucap='cv') then pc.hs_cu  else 0 end) ";
            sql+=" + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end )) ) else 0 end )* (case when ( (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )  then (he.sbhxh_hs+he.mbhxh_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) then (he.sbhyt_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs)  else 0 end)=( he.sbhxh_hs+he.mbhxh_hs +he.sbhyt_hs+he.mbhyt_hs+he.sbhtn_hs+he.mbhtn_hs ) ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  then (he.sbhyt_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=( he.sbhxh_hs+he.mbhxh_hs +he.sbhyt_hs+he.mbhyt_hs ) ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )   then (he.sbhxh_hs+he.mbhxh_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  then (he.sbhyt_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs) else 0 end)=( he.sbhtn_hs+he.mbhtn_hs) ) "; 
            sql+=" then ( he.sbhtn_hs+he.mbhtn_hs) else 0 end ) * (date_part('year', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy')))*12 ";
            sql+=" + date_part('month', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy'))))* he.mltt  as bs_bhxh_giam  ";

            sql+=" ,(case when ( (hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn+hd.hs_kv) -  ( to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') ";
            sql+=" then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end)  + ( case when( pc.loaiphucap='tn') ";
            sql+=" then pc.hs_cu  else 0 end )+ ( case when( pc.loaiphucap='kv') then pc.hs_cu  else 0 end )))> 0  then   (hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn+hd.hs_kv) ";
            sql+=" -  ( to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv')  then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end) "; 
            sql+=" + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end )+  ( case when( pc.loaiphucap='kv') then pc.hs_cu  else 0 end )) ";  
            sql+=" else 0 end ) *  (case when ( (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )  then (he.sbhxh_hs+he.mbhxh_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) then (he.sbhyt_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs)  else 0 end)=( he.sbhxh_hs+he.mbhxh_hs +he.sbhyt_hs+he.mbhyt_hs+he.sbhtn_hs+he.mbhtn_hs ) ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  then (he.sbhyt_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=( he.sbhxh_hs+he.mbhxh_hs +he.sbhyt_hs+he.mbhyt_hs ) ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )   then (he.sbhxh_hs+he.mbhxh_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  then (he.sbhyt_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs) else 0 end)=( he.sbhtn_hs+he.mbhtn_hs) ) "; 
            sql+=" then ( he.sbhtn_hs+he.mbhtn_hs) else 0 end ) * (date_part('year', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy')))*12 ";
            sql+=" + date_part('month', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy')))) * he.mltt  as bs_bhyt_tang ";

            sql+=" ,( case when  ( to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') then pc.hs_cu  else 0 end) ";
            sql+=" + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end) +  ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end ) ";
            sql+=" +  ( case when( pc.loaiphucap='kv') then pc.hs_cu  else 0 end ) -(hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn+hd.hs_kv)) > 0  then ( to_number(split_part(bl.bacluongcu,' | ',2)) ";
            sql+=" + ( case when( pc.loaiphucap='cv') then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end)  +( case when( pc.loaiphucap='tn') ";
            sql+=" then pc.hs_cu  else 0 end )+  ( case when( pc.loaiphucap='kv') then pc.hs_cu  else 0 end )  -(hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn+hd.hs_kv) )  else 0 end  ) ";
            sql+=" * ( case when ((case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  ";
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )  then (he.sbhxh_hs+he.mbhxh_hs)  ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) "; 
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1)  then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=( he.sbhxh_hs+he.mbhxh_hs +he.sbhyt_hs+he.mbhyt_hs+he.sbhtn_hs+he.mbhtn_hs ) ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  ";
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs)  ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) then (he.sbhyt_hs+he.mbhyt_hs)  ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) then (he.sbhtn_hs+he.mbhtn_hs) else 0 end)=( he.sbhxh_hs+he.mbhxh_hs +he.sbhyt_hs+he.mbhyt_hs )  ";
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  ";
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  ";
            sql+=" when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) then (he.sbhxh_hs+he.mbhxh_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) then (he.sbhyt_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 ) then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=( he.sbhyt_hs+he.mbhyt_hs ) )  then  ( he.sbhyt_hs+he.mbhyt_hs )  else  0 end ) ";
            sql+=" * (date_part('year', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy')))*12 ";
            sql+=" + date_part('month', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy')))) * he.mltt  as bs_bhyt_giam ";

            sql+=" ,(((case when  ((hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn+hd.hs_kv ) -  (to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') then pc.hs_cu  else 0 end) ";  
            sql+=" + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end )  + ( case when( pc.loaiphucap='kv') ";
            sql+=" then pc.hs_cu  else 0 end )))  > 0  then   (hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn+hd.hs_kv ) -  (to_number(split_part(bl.bacluongcu,' | ',2)) ";  
            sql+=" + ( case when( pc.loaiphucap='cv') then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end) "; 
            sql+=" + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end )+  ( case when( pc.loaiphucap='kv') then pc.hs_cu  else 0 end ))  else 0 end ) ";  
            sql+=" * (case when ((case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  ";
            sql+=" and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )   then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 )  then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) ";  
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=( he.sbhxh_hs+he.mbhxh_hs +he.sbhyt_hs+he.mbhyt_hs+he.sbhtn_hs+he.mbhtn_hs ) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) ";
            sql+=" is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) ";  
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) "; 
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=( he.sbhxh_hs+he.mbhxh_hs+he.sbhtn_hs+he.mbhtn_hs )   or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)   when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.mbhxh_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) ";  
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=( he.sbhtn_hs+he.mbhtn_hs ) ) then ( he.sbhtn_hs+he.mbhtn_hs )  else 0 end ) ";
            sql+=" *  (date_part('year', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy')))*12 ";
            sql+=" + date_part('month', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy'))))) *  he.mltt  ) as  bs_bhtn_tang ";
             
            sql+=" ,(((case when (-(hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn+ hd.hs_kv)+(to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') then pc.hs_cu  else 0 end) ";  
            sql+=" + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end )  + ( case when( pc.loaiphucap='kv') then pc.hs_cu  ";
            sql+=" else 0 end ))) >0  then   (-(hd.hs_lc+hd.hs_cv+hd.hs_vk+hd.hs_tn+ hd.hs_kv)   + (to_number(split_part(bl.bacluongcu,' | ',2)) + ( case when( pc.loaiphucap='cv') ";
            sql+=" then pc.hs_cu  else 0 end)   + ( case when( pc.loaiphucap='vk') then pc.hs_cu  else 0 end) + ( case when( pc.loaiphucap='tn') then pc.hs_cu  else 0 end ) "; 
            sql+=" + ( case when( pc.loaiphucap='kv') then pc.hs_cu  else 0 end ))) else  0 end )   * ( case when ((case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) "; 
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.mbhxh_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  then (he.sbhyt_hs+he.mbhyt_hs) ";  
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) "; 
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=( he.sbhxh_hs+he.mbhxh_hs +he.sbhyt_hs+he.mbhyt_hs+he.sbhtn_hs+he.mbhtn_hs ) or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) ";
            sql+=" is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  ";
            sql+=" when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )  "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
            sql+=" then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )  ";
            sql+=" then (he.sbhxh_hs+he.mbhxh_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  ";
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null ";
            sql+=" and hs.bhtn=1)   then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=( he.sbhxh_hs+he.mbhxh_hs+he.sbhtn_hs+he.mbhtn_hs ) ";  
            sql+=" or (case  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) "; 
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null  ";
            sql+=" and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null ";
            sql+=" and hs.bhxh=1 and hs.bhyt=1 )  then (he.sbhxh_hs+he.mbhxh_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1) "; 
            sql+=" then (he.sbhyt_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";  
            sql+=" then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs)  when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 ";
            sql+=" and hs.bhyt=1 and hs.bhtn=0 )   then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs)   when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";  
            sql+=" then (he.sbhtn_hs+he.mbhtn_hs)   else 0 end)=(he.sbhtn_hs+he.mbhtn_hs ) )  then  (he.sbhtn_hs+he.mbhtn_hs )  else  0 end ) ";
            sql+=" * (date_part('year', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy')))*12 ";
            sql+=" + date_part('month', age(to_timestamp('" + denngay + "','dd/mm/yyyy'),to_timestamp(to_char(hd.ngaybhxh,'dd/mm/yyyy'),'dd/mm/yyyy')))) ) ";
            sql+=" * he.mltt ) as bs_bhtn_giam ";
            sql += " from " + user + ".llnv l  ";
            sql += " left join " + user + ".hopdong hd on l.manv=hd.manv ";
            sql += " left join " + user + ".thebhyt yt on l.manv=yt.manv  ";
            sql += " left join " + user + ".btdtt tt on l.t_diachi=tt.matt ";
            sql += " left join " + user + ".btdquan qu on l.h_diachi=qu.maqu  ";
            sql += " left join " + user + ".benhvien bv on bv.mabv=yt.tai_bvien ";
            sql += " left join " + user + ".thaydoi_bluong bl on hd.manv=bl.manv ";
            sql += " left join " + user + ".thaydoi_phucap pc on pc.manv=l.manv ";
            sql += " left join " + user + ".heso_nv_" + s_mmyy + " hs on l.manv=hs.manv ";
            sql += " inner join " + user + ".heso he on hs.mmyy=he.mmyy ";
            sql += " inner join " + user + ".max_lan ml on hd.manv=ml.manv  ";
            sql+=" where (hd.bhyt=1 or hd.bhxh=1 or hd.bhtn=1) and ( pc.loaiphucap='vk' or pc.loaiphucap='cv' or pc.loaiphucap='tn' or pc.loaiphucap='kv') "; 
            sql+=" and (hd.ngaynghi is null or to_timestamp(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy') >= to_timestamp('" + denngay + "','dd/mm/yyyy') ) "; 
            sql+=" and to_timestamp(to_char(hd.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy') < to_timestamp('" + denngay + "','dd/mm/yyyy') ";
            sql += " and hd.lan=ml.lan and (to_timestamp (to_char(bl.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy') between to_timestamp ('" + tungay + "','dd/mm/yyyy') "; 
            sql+=" and to_timestamp('" + denngay + "','dd/mm/yyyy')) ";
            return ds_mau03a = mDB.get_data(sql);
#endregion 

            
#region code cu
            //string sql = " select 0 as stt,trim(l.ho)||' '||trim(l.ten) as hoten,hd.sobhxh,to_char(l.ngaysinh,'dd/mm/yyyy') as ngaysinh";
            //sql+=" ,split_part(bl.bacluongcu,' | ',2) as hslc_cu,0.00 as hscv_cu,0.00 as hsvk_cu,0.0 as hstn_cu,0.0 as hskv_cu";
            //sql+=" ,hd.hs_lc,hd.hs_cv,hd.hs_vk,hd.hs_tn,hd.hs_kv";             
            //sql+=" ,bl.mmyy,to_char(bl.ngaynangbac,'mm/yy') as tuthang,'' as denthang";           
            //sql+=" ,0 as tyle,case when hd.ngaynghi <> null then 'X' else '' as trathe,'Nâng lương' as ghichu,yt.sobhyt,hd.bhxh ,hd.bhyt,hd.bhtn,l.manv";        
            //sql+=" ,to_char(hd.ngaybhxh,'mm/yyyy') as thangbhxh";  
            //sql+=" from " + user + ".llnv l ";
            //sql+=" left join " + user + ".hopdong hd on l.manv=hd.manv";
            //sql+=" left join " + user + ".thebhyt yt on l.manv=yt.manv";
            //sql+=" left join " + user + ".btdtt tt on l.t_diachi=tt.matt";
            //sql+=" left join " + user + ".btdquan qu on l.h_diachi=qu.maqu";
            //sql+=" left join " + user + ".benhvien bv on bv.mabv=yt.tai_bvien";
            //sql+=" left join " + user + ".thaydoi_bluong bl on hd.manv=bl.manv";
            //sql+=" left join " + user + ".heso_nv_" + s_mmyy + " hs on l.manv=hs.manv";
            //sql+=" inner join " + user + ".heso he on hs.mmyy=he.mmyy";
            //sql+=" inner join " + user + ".max_lan ml on hd.manv=ml.manv";
            //sql+=" where (hd.bhyt=1 or hd.bhxh=1 or hd.bhtn=1) and (hd.ngaynghi is null or  to_timestamp(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy') >= to_timestamp('" + denngay + "','dd/mm/yyyy') )";
            //sql+=" and to_timestamp(to_char(hd.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy') < to_timestamp('" + denngay + "','dd/mm/yyyy')   and hd.lan=ml.lan";
            //sql+=" and (to_timestamp(to_char(bl.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy') between to_timestamp('" + tungay + "','dd/mm/yyyy') and to_timestamp('" + denngay + "','dd/mm/yyyy'))";
            
            //sql+=" union ";
            //sql+=" select 0 as stt,trim(l.ho)||' '||trim(l.ten) as hoten,hd.sobhxh,to_char(l.ngaysinh,'dd/mm/yyyy') as ngaysinh";
            //sql+=" ,'0.00' as hslc_cu,0.00 as hscv_cu,0.00 as hsvk_cu,0.0 as hstn_cu,0.0 as hskv_cu";
            //sql+=" ,hd.hs_lc,hd.hs_cv,hd.hs_vk,hd.hs_tn,hd.hs_kv";
            //sql+=" ,bl.mmyy,to_char(hd.ngaynghi,'mm/yy') as tuthang,'' as denthang";
            //sql+=" ,0 as tyle,false as trathe,'Nghỉ việc' as ghichu,yt.sobhyt,hd.bhxh ,hd.bhyt,hd.bhtn,l.manv";   
            //sql+=" ,to_char(hd.ngaybhxh,'mm/yyyy') as thangbhxh";
            //sql+=" from " + user + ".llnv l ";
            //sql+=" left join " + user + ".hopdong hd on l.manv=hd.manv";
            //sql+=" left join " + user + ".thebhyt yt on l.manv=yt.manv";
            //sql+=" left join " + user + ".btdtt tt on l.t_diachi=tt.matt";
            //sql+=" left join " + user + ".btdquan qu on l.h_diachi=qu.maqu";
            //sql+=" left join " + user + ".benhvien bv on bv.mabv=yt.tai_bvien";
            //sql+=" left join " + user + ".thaydoi_bluong bl on hd.manv=bl.manv";
            //sql+=" left join " + user + ".heso_nv_" + s_mmyy + " hs on l.manv=hs.manv";
            //sql+=" inner join " + user + ".heso he on hs.mmyy=he.mmyy ";
            //sql+=" inner join " + user + ".max_lan ml on hd.manv=ml.manv";
            //sql+=" where (hd.bhyt=1 or hd.bhxh=1 or hd.bhtn=1) and to_timestamp(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy') <= to_timestamp('" + tungay + "','dd/mm/yyyy') and to_timestamp(hd.ngaynghi,'dd/mm/yyyy') <= to_timestamp('" + denngay + "','dd/mm/yyyy') ";
            //sql+=" and to_timestamp(to_char(hd.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy') <= to_timestamp('" + denngay + "','dd/mm/yyyy')   and hd.lan=ml.lan";
            
            //sql+=" union";
            //sql+=" select 0 as stt,trim(l.ho)||' '||trim(l.ten) as hoten,hd.sobhxh,to_char(l.ngaysinh,'dd/mm/yyyy') as ngaysinh";
            //sql+=" ,'0.00' as hslc_cu,case when pc.loaiphucap='cv' then pc.hs_cu else 0 end as hscv_cu,case when pc.loaiphucap='vk' then pc.hs_cu else 0 end as hsvk_cu";
            //sql+=" ,0.0 as hstn_cu,0.0 as hskv_cu";
            //sql+=" ,0.00 as hs_lc,hd.hs_cv,hd.hs_vk,hd.hs_tn,hd.hs_kv";            
            //sql+=" ,pc.mmyy,to_char(pc.ngaygiu,'mm/yy') as tuthang,'' as denthang";           
            //sql+=" ,0 as tyle,false as trathe,case when pc.loaiphucap='vk' then 'Nâng vượt khung' when pc.loaiphucap='cv' then 'Thay đổi PC chức vụ' end as ghichu,yt.sobhyt,hd.bhxh ,hd.bhyt,hd.bhtn,l.manv";
            //sql+=" ,to_char(hd.ngaybhxh,'mm/yyyy') as thangbhxh";
            //sql+=" from " + user + ".llnv l "; 
            //sql+=" left join " + user + ".hopdong hd on l.manv=hd.manv";
            //sql+=" left join " + user + ".thebhyt yt on l.manv=yt.manv";
            //sql+=" left join " + user + ".btdtt tt on l.t_diachi=tt.matt";
            //sql+=" left join " + user + ".btdquan qu on l.h_diachi=qu.maqu";
            //sql+=" left join " + user + ".benhvien bv on bv.mabv=yt.tai_bvien";
            //sql+=" left join " + user + ".thaydoi_phucap pc on hd.manv=pc.manv";
            //sql+=" left join " + user + ".heso_nv_" + s_mmyy + " hs on l.manv=hs.manv";
            //sql+=" inner join " + user + ".heso he on hs.mmyy=he.mmyy ";
            //sql+=" inner join " + user + ".max_lan ml on hd.manv=ml.manv";
            //sql+=" where (hd.bhyt=1 or hd.bhxh=1 or hd.bhtn=1) and (hd.ngaynghi is null or to_timestamp(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy') >= to_timestamp('" + denngay + "','dd/mm/yyyy'))";
            //sql+=" and to_timestamp(to_char(hd.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy') < to_timestamp('" + denngay + "','dd/mm/yyyy') and hd.lan=ml.lan ";
            //sql+=" and (to_timestamp(to_char(pc.ngaygiu,'dd/mm/yyyy'),'dd/mm/yyyy') between to_timestamp('" + tungay + "','dd/mm/yyyy') and to_timestamp('" + denngay + "','dd/mm/yyyy'))";
           
            //sql += " order by manv";
            //ds = mDB.get_data(sql);
            
            //if (ds != null)
            //{
            //    ds.WriteXml("..\\xml\\h_r03a_dsld_thamgiabhyt,bhxh_quy .xml", XmlWriteMode.WriteSchema);              
            //    for (int i = 0; i < ds.Tables[0].Rows.Count-1; i++)
            //    {
            //        if (ds.Tables[0].Rows[i]["manv"].ToString() != ds.Tables[0].Rows[i + 1]["manv"].ToString())
            //        {
            //            ds.Tables[0].Rows[i]["stt"] = stt.ToString();
            //            stt++;
            //        }
            //    }
            //    dgvDanhSach.DataSource = ds.Tables[0];
            //    cmbhoten.DisplayMember = "hoten";
            //    cmbhoten.ValueMember = "manv";
            //    cmbhoten.DataSource = ds.Tables[0];
            //    txttongso.Text = ds.Tables[0].Rows.Count.ToString();
            //}
#endregion 
        }
        private bool kiemtra()
        {
            tungay = haison1.tungay;
            denngay = haison1.denngay;
            DateTime dt1 = mDB.StringToDate(tungay);
            DateTime dt2 = mDB.StringToDate(denngay);
            int y1 = dt1.Year, m1 = dt1.Month;
            int y2 = dt2.Year, m2 = dt2.Month;
            int itu, iden;
            string mmyy = "";
            for (int i = y1; i <= y2; i++)
            {
                itu = (i == y1) ? m1 : 1;
                iden = (i == y2) ? m2 : 12;
                for (int j = itu; j <= iden; j++)
                {
                    mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2);
                    if (mDB.bMmyy(mmyy))
                    {
                        s_mmyy = mmyy;
                        ds_nv.Merge(get_hoten(s_mmyy));
                        ds_dcbhxh.Merge(get_ds_bhxh_bhyt(s_mmyy));
                        ds_kq_mau03a.Merge(get_mau03a_bhxh_bhyt(s_mmyy));
                    }
                }
            }
            return true;
        }
        private DataSet get_hoten(string s_mmyy)
        {
            DataSet ds_hoten = new DataSet();
            
                string sql = "select distinct l.manv,l.ho||' '||l.ten as hoten ";
                sql += " from " + user + ".llnv l  ";
                sql += " left join " + user + ".hopdong hd on l.manv=hd.manv ";
                sql += " left join " + user + ".thebhyt yt on l.manv=yt.manv  ";
                sql += " left join " + user + ".btdtt tt on l.t_diachi=tt.matt ";
                sql += " left join " + user + ".btdquan qu on l.h_diachi=qu.maqu  ";
                sql += " left join " + user + ".benhvien bv on bv.mabv=yt.tai_bvien ";
                sql += " left join " + user + ".thaydoi_bluong bl on hd.manv=bl.manv ";
                sql += " left join " + user + ".thaydoi_phucap pc on pc.manv=l.manv ";
                sql += " left join " + user + ".heso_nv_" + s_mmyy + " hs on l.manv=hs.manv ";
                sql += " inner join " + user + ".heso he on hs.mmyy=he.mmyy ";
                sql += " inner join " + user + ".max_lan ml on hd.manv=ml.manv  ";
                sql += " where (hd.bhyt=1 or hd.bhxh=1 or hd.bhtn=1) and ( pc.loaiphucap='vk' or pc.loaiphucap='cv' or pc.loaiphucap='tn' or pc.loaiphucap='kv') ";
                sql += " and (hd.ngaynghi is null or to_timestamp(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy') >= to_timestamp('" + denngay + "','dd/mm/yyyy') ) ";
                sql += " and to_timestamp(to_char(hd.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy') < to_timestamp('" + denngay + "','dd/mm/yyyy') ";
                sql += " and hd.lan=ml.lan and (to_timestamp (to_char(bl.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy') between to_timestamp ('" + tungay + "','dd/mm/yyyy') ";
                sql += " and to_timestamp('" + denngay + "','dd/mm/yyyy')) ";
                return ds_hoten = mDB.get_data(sql);
        }
        public DataSet get_ds_bhxh_bhyt(string s_mmyy)
        {
            DataSet dsdc = new DataSet();
            string sql = "";
                sql = " select 0 as stt,trim(l.ho)||' '||trim(l.ten) as hoten,hd.sobhxh as so_bhxh,to_char(l.ngaysinh,'dd/mm/yyyy') as ngaysinh ,split_part(bl.bacluongcu,' | ',2) as hslc_cu ";
                sql += " ,case when( pc.loaiphucap='cv') then pc.hs_cu  else 0 end as hscv_cu ,case when ( pc.loaiphucap='vk') then pc.hs_cu else 0 end as hsvk_cu,case when ( pc.loaiphucap='vk') then pc.hs_cu else 0 end as hsvk_cu,case when ( pc.loaiphucap='tn') then pc.hs_cu else 0 end as hstn_cu ";
                sql += " ,case when ( pc.loaiphucap='kv') then pc.hs_cu else 0 end as hskv_cu,hd.hs_lc,case when ( pc.loaiphucap='cv') then pc.hs_moi else 0 end as hs_cv,case when ( pc.loaiphucap='vk') then pc.hs_moi else 0 end as hs_vk,case when ( pc.loaiphucap='tn') then pc.hs_moi else 0 end as hs_tn ";
                sql += " ,case when ( pc.loaiphucap='kv') then pc.hs_moi else 0 end as hs_kv,to_char(hd.ngaybhxh,'mm/yy') as thangnam_bhxh,hs.mmyy as dong_den_thang_nam ";
                // tinh ty le bo sung
                // Dieu chinh do tang giam luong (muc luong cu <> 0 va muc luong moi <> 0) co tham gia : bhxh,bhyt,bhtn (28.5 %)
                sql += " ,case when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 ) ";
                sql += " then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";
                // Dieu chinh do tang giam luong (muc luong cu <> 0 va muc luong moi <> 0) co tham gia : bhxh,bhyt (26.5 %)
                sql += " when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is not null and hd.bhyt=1 and hs.bhxh=1 and hd.bhtn=0 )  ";
                sql += " then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";
                // Giam tham gia ( ke ca giam nguyen luong): muc luong cu <> 0 va muc luong moi = 0 va co tra the BHYT kip thoi ,co tham gia BHXH,BHTN (24%)
                sql += " when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
                sql += " then (he.sbhxh_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhtn_hs)  ";
                // Giam tham gia ( ke ca giam nguyen luong): muc luong cu <> 0 va muc luong moi = 0 va co tra the BHYT kip thoi ,co tham gia BHXH (22%)
                sql += " when ( hd.hs_lc is null and split_part(bl.bacluongcu,' | ',2) is not null and hs.bhxh=1 and hs.bhyt=1 )  ";
                sql += " then (he.sbhxh_hs+he.mbhxh_hs) ";
                // Bo sung tang /giam BHYT : tra the BHYT khong kip thoi,khong tra the BHYT (4.5%)
                sql += " when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1)  ";
                sql += " then (he.sbhyt_hs+he.mbhyt_hs) ";
                // Bo sung tang nguyen luong :muc luong cu =0 va muc luong moi <>0 va co tham gia BHXH,BHYT,BHTN (28.5%)
                sql += " when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=1 )  ";
                sql += " then (he.sbhxh_hs+he.sbhyt_hs+he.sbhtn_hs+he.mbhxh_hs+he.mbhyt_hs+he.mbhtn_hs) ";
                // Bo sung tang nguyen luong :muc luong cu =0 va muc luong moi <>0 va co tham gia BHXH,BHYT (26.5%)
                sql += " when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhxh=1 and hs.bhyt=1 and hs.bhtn=0 )  ";
                sql += " then (he.sbhxh_hs+he.sbhyt_hs+he.mbhxh_hs+he.mbhyt_hs) ";
                // Tang tham gia that nghiep :muc luong cu=0 va muc luong moi <>0 va co tham gia BHTN (2%)
                sql += " when ( hd.hs_lc is not null and split_part(bl.bacluongcu,' | ',2) is null and hs.bhtn=1) ";
                sql += " then (he.sbhtn_hs+he.mbhtn_hs) ";
                sql += " else 0 end as tyle_bosung ";
                sql += ",case when hd.ngaynghi is not null then true else false end as trathe,'Nâng luong' as ghichu ";
                sql += " from " + user + ".llnv l  ";
                sql += " left join " + user + ".hopdong hd on l.manv=hd.manv ";
                sql += " left join " + user + ".thebhyt yt on l.manv=yt.manv  ";
                sql += " left join " + user + ".btdtt tt on l.t_diachi=tt.matt ";
                sql += " left join " + user + ".btdquan qu on l.h_diachi=qu.maqu  ";
                sql += " left join " + user + ".benhvien bv on bv.mabv=yt.tai_bvien ";
                sql += " left join " + user + ".thaydoi_bluong bl on hd.manv=bl.manv ";
                sql += " left join " + user + ".thaydoi_phucap pc on pc.manv=l.manv ";
                sql += " left join " + user + ".heso_nv_" + s_mmyy + " hs on l.manv=hs.manv ";
                sql += " inner join " + user + ".heso he on hs.mmyy=he.mmyy ";
                sql += " inner join " + user + ".max_lan ml on hd.manv=ml.manv  ";
                sql += " where (hd.bhyt=1 or hd.bhxh=1 or hd.bhtn=1) and ( pc.loaiphucap='vk' or pc.loaiphucap='cv' or pc.loaiphucap='tn' or pc.loaiphucap='kv') ";
                sql += " and (hd.ngaynghi is null or to_timestamp(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy') >= to_timestamp('" + denngay + "','dd/mm/yyyy') ) ";
                sql += " and to_timestamp(to_char(hd.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy') < to_timestamp('" + denngay + "','dd/mm/yyyy') ";
                sql += " and hd.lan=ml.lan and (to_timestamp (to_char(bl.ngaynangbac,'dd/mm/yyyy'),'dd/mm/yyyy') between to_timestamp ('" + tungay + "','dd/mm/yyyy') ";
                sql += " and to_timestamp('" + denngay + "','dd/mm/yyyy')) ";
                return dsdc = mDB.get_data(sql);

        } 
        private void load_hoten()
        {
            cmbhoten.DisplayMember = "hoten";
            cmbhoten.ValueMember = "manv";
            cmbhoten.DataSource = ds_nv.Tables[0];
            txtmanv.Text = cmbhoten.SelectedValue.ToString();
        }
        private void load_dcbhxh()
        {
            int i = 0;
            if (kiemtra() == true)
            {
                if (ds_kq_mau03a.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow r in ds_kq_mau03a.Tables[0].Rows)
                    {
                        i++;
                        r["stt"] = i.ToString();
                    }
                    dgvDanhSach.DataSource = ds_dcbhxh.Tables[0];
                    //dgvDanhSach.DataSource = ds_kq_mau03a.Tables[0];
                    load_hoten();
                }
            }
        }
        private DataSet tonghop()
        {
            kiemtra();
            ds_ts_mau03a.Clear();
            ds_ts_mau03a = tao_dataset();
            DataRow nrow, dr2;
            
            decimal sum_ps_bhxh_tang = 0, sum_ps_bhxh_giam = 0, sum_ps_bhyt_tang = 0, sum_ps_bhyt_giam = 0, sum_ps_bhtn_tang = 0, sum_ps_bhtn_giam = 0,
                                   sum_bs_bhxh_tang = 0, sum_bs_bhxh_giam = 0, sum_bs_bhyt_tang = 0, sum_bs_bhyt_giam = 0, sum_bs_bhtn_tang = 0, sum_bs_bhtn_giam = 0
                                   , ps_bhxh_tang = 0, ps_bhxh_giam = 0, ps_bhyt_tang = 0, ps_bhyt_giam = 0, ps_bhtn_tang = 0, ps_bhtn_giam = 0
                                   , bs_bhxh_tang = 0, bs_bhxh_giam = 0, bs_bhyt_tang = 0, bs_bhyt_giam = 0, bs_bhtn_tang = 0, bs_bhtn_giam = 0, he_mltt = 0;
            decimal sum_sld_bhxh_bhyt_tang = 0, sum_sld_bhxh_bhyt_giam = 0, sum_sld_bhtn_tang = 0, sum_sld_bhtn_giam = 0, sld_bhxh_bhyt_tang = 0, sld_bhxh_bhyt_giam = 0
                    ,sld_bhtn_tang = 0,sld_bhtn_giam = 0;
            int dong = -1;
            // duyet de tinh tong
            if (ds_kq_mau03a.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds_kq_mau03a.Tables[0].Rows)
                {
                    sum_ps_bhxh_tang = decimal.Parse(r["ps_bhxh_tang"].ToString());
                    ps_bhxh_tang += sum_ps_bhxh_tang;
                    sum_ps_bhxh_giam = decimal.Parse(r["ps_bhxh_giam"].ToString());
                    ps_bhxh_giam += sum_ps_bhxh_giam;
                    sum_ps_bhyt_tang = decimal.Parse(r["ps_bhyt_tang"].ToString());
                    ps_bhyt_tang += sum_ps_bhyt_tang;
                    sum_ps_bhyt_giam = decimal.Parse(r["ps_bhyt_giam"].ToString());
                    ps_bhyt_giam += sum_ps_bhyt_giam;

                    sum_ps_bhtn_tang = decimal.Parse(r["ps_bhtn_tang"].ToString());
                    ps_bhtn_tang += sum_ps_bhtn_tang;
                    sum_ps_bhtn_giam = decimal.Parse(r["ps_bhtn_giam"].ToString());
                    ps_bhtn_giam += sum_ps_bhtn_giam;

                    sum_bs_bhxh_tang = decimal.Parse(r["bs_bhxh_tang"].ToString());
                    bs_bhxh_tang += sum_bs_bhxh_tang;
                    sum_bs_bhxh_giam = decimal.Parse(r["bs_bhxh_giam"].ToString());
                    bs_bhxh_giam += sum_bs_bhxh_giam;

                    sum_bs_bhyt_tang = decimal.Parse(r["bs_bhyt_tang"].ToString());
                    bs_bhyt_tang += sum_bs_bhyt_tang;
                    sum_bs_bhyt_giam = decimal.Parse(r["bs_bhyt_giam"].ToString());
                    bs_bhyt_giam += sum_bs_bhyt_giam;

                    sum_bs_bhtn_tang = decimal.Parse(r["bs_bhtn_tang"].ToString());
                    bs_bhtn_tang += sum_bs_bhtn_tang;
                    sum_bs_bhtn_giam = decimal.Parse(r["bs_bhtn_giam"].ToString());
                    bs_bhtn_giam += sum_bs_bhtn_giam;

                    sum_sld_bhxh_bhyt_tang = decimal.Parse(r["sld_bhxh_bhyt_tang"].ToString());
                    sld_bhxh_bhyt_tang += sum_sld_bhxh_bhyt_tang;
                    sum_sld_bhxh_bhyt_giam = decimal.Parse(r["sld_bhxh_bhyt_giam"].ToString());
                    sld_bhxh_bhyt_giam += sum_sld_bhxh_bhyt_giam;
                    sum_sld_bhtn_tang = decimal.Parse(r["sld_bhtn_tang"].ToString());
                    sld_bhtn_tang += sum_sld_bhtn_tang;
                    sum_sld_bhtn_giam = decimal.Parse(r["sld_bhtn_giam"].ToString());
                    sld_bhtn_giam += sum_sld_bhtn_giam;

                    he_mltt = decimal.Parse(r["mltt"].ToString());
                }
                //
                dr2 = ds_ts_mau03a.Tables["Table1"].NewRow();

                dr2["tong_ps_bhxh_tang"] = ps_bhxh_tang.ToString();
                dr2["tong_ps_bhxh_giam"] = ps_bhxh_giam.ToString();
                dr2["tong_ps_bhyt_tang"] = ps_bhyt_tang.ToString();
                dr2["tong_ps_bhyt_giam"] = ps_bhyt_giam.ToString();
                dr2["tong_ps_bhtn_tang"] = ps_bhtn_tang.ToString();
                dr2["tong_ps_bhtn_giam"] = ps_bhtn_giam.ToString();
                //
                dr2["tong_bs_bhxh_tang"] = bs_bhxh_tang.ToString();
                dr2["tong_bs_bhxh_giam"] = bs_bhxh_giam.ToString();
                dr2["tong_bs_bhyt_tang"] = bs_bhyt_tang.ToString();
                dr2["tong_bs_bhyt_giam"] = bs_bhyt_giam.ToString();
                dr2["tong_bs_bhtn_tang"] = bs_bhtn_tang.ToString();
                dr2["tong_bs_bhtn_giam"] = bs_bhtn_giam.ToString();
                //
                dr2["ts_ld_bhxh_tang"] = sld_bhxh_bhyt_tang.ToString();
                dr2["ts_ld_bhxh_giam"] = sld_bhxh_bhyt_giam.ToString();
                dr2["ts_ld_bhyt_tang"] = sld_bhxh_bhyt_tang.ToString();
                dr2["ts_ld_bhyt_giam"] = sld_bhxh_bhyt_giam.ToString();
                dr2["ts_ld_bhtn_tang"] = sld_bhtn_tang.ToString();
                dr2["ts_ld_bhtn_giam"] = sld_bhtn_giam.ToString();
                //
                dr2["quyluong_bhxh_tang"] = decimal.Parse(dr2["tong_ps_bhxh_tang"].ToString()) * he_mltt;
                dr2["quyluong_bhxh_giam"] = decimal.Parse(dr2["tong_ps_bhxh_giam"].ToString()) * he_mltt;
                dr2["quyluong_bhyt_tang"] = decimal.Parse(dr2["tong_ps_bhyt_tang"].ToString()) * he_mltt;
                dr2["quyluong_bhyt_giam"] = decimal.Parse(dr2["tong_ps_bhyt_giam"].ToString()) * he_mltt;
                dr2["quyluong_bhtn_tang"] = decimal.Parse(dr2["tong_ps_bhtn_tang"].ToString()) * he_mltt;
                dr2["quyluong_bhtn_giam"] = decimal.Parse(dr2["tong_ps_bhtn_giam"].ToString()) * he_mltt;
                //
                dr2["phaidong_bhxh_tang"] = decimal.Parse(dr2["quyluong_bhxh_tang"].ToString()) * Convert.ToDecimal(0.2); // 20%
                dr2["phaidong_bhxh_giam"] = decimal.Parse(dr2["quyluong_bhxh_giam"].ToString()) * Convert.ToDecimal(0.2); // 20%
                dr2["phaidong_bhyt_tang"] = decimal.Parse(dr2["quyluong_bhyt_tang"].ToString()) * Convert.ToDecimal(0.03); // 3%
                dr2["phaidong_bhyt_giam"] = decimal.Parse(dr2["quyluong_bhyt_giam"].ToString()) * Convert.ToDecimal(0.03); // 3%
                dr2["phaidong_bhtn_tang"] = decimal.Parse(dr2["quyluong_bhtn_tang"].ToString()) * Convert.ToDecimal(0.02); // 2%
                dr2["phaidong_bhtn_giam"] = decimal.Parse(dr2["quyluong_bhtn_giam"].ToString()) * Convert.ToDecimal(0.02); // 2%
                //
                dr2["dieuchinh_bhxh_tang"] = dr2["tong_bs_bhxh_tang"].ToString();
                dr2["dieuchinh_bhxh_giam"] = dr2["tong_bs_bhxh_giam"].ToString();
                dr2["dieuchinh_bhyt_tang"] = dr2["tong_bs_bhyt_tang"].ToString();
                dr2["dieuchinh_bhyt_giam"] = dr2["tong_bs_bhyt_giam"].ToString();
                dr2["dieuchinh_bhtn_tang"] = dr2["tong_bs_bhtn_tang"].ToString();
                dr2["dieuchinh_bhtn_giam"] = dr2["tong_bs_bhtn_giam"].ToString();
                ds_ts_mau03a.Tables["Table1"].Rows.Add(dr2);

            }
            if (ds_kq_mau03a.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds_mau03a.Tables[0].Rows)
                {
                    DataRow[] dr1 = ds_kq_mau03a.Tables[0].Select("manv= '" + dr["manv"].ToString() + "'", "mltt asc");
                    string str = dr1[0]["manv"].ToString();
                    decimal kt_heso_luong = 0, kt_hscv = 0, kt_hsvk = 0, kt_hstn = 0, kt_hskv = 0, kt_tyle_bhxh = 0, kt_tyle_bhyt = 0, kt_tyle_bhtn = 0;
                    string kt_ngaynghi = "", kt_trathe = "";
                    if (dr1.Length > 0)
                    {
                        kt_heso_luong = dr1[0]["hs_lc"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["hs_lc"].ToString().Trim()) : 0;
                        kt_hscv = dr1[0]["hs_cv"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["hs_cv"].ToString().Trim()) : 0;
                        kt_hsvk = dr1[0]["hs_vk"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["hs_vk"].ToString().Trim()) : 0;
                        kt_hstn = dr1[0]["hs_tn"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["hs_tn"].ToString().Trim()) : 0;
                        kt_hskv = dr1[0]["hs_kv"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["hs_kv"].ToString().Trim()) : 0;
                        kt_tyle_bhxh = dr1[0]["mbhxh_hs"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["mbhxh_hs"].ToString().Trim()) : 0;
                        kt_tyle_bhyt = dr1[0]["mbhyt_hs"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["mbhyt_hs"].ToString().Trim()) : 0;
                        kt_tyle_bhtn = dr1[0]["mbhtn_hs"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["mbhtn_hs"].ToString().Trim()) : 0;
                        kt_ngaynghi = dr1[0]["ngaynghi"].ToString().Trim() != "" ? dr1[0]["ngaynghi"].ToString().Trim() : "";
                        kt_trathe = dr1[0]["trathe"].ToString().Trim() != "" ? dr1[0]["trathe"].ToString().Trim() : "";

                    }
                    foreach (DataRow r in dr1)
                    {
                        if (kt_heso_luong < decimal.Parse(r["hs_lc"].ToString().Trim())
                            || kt_hscv != decimal.Parse(r["hs_cv"].ToString().Trim())
                            || kt_hsvk != decimal.Parse(r["hs_vk"].ToString().Trim())
                            || kt_hstn != decimal.Parse(r["hs_tn"].ToString().Trim())
                            || kt_hskv != decimal.Parse(r["hs_kv"].ToString().Trim())
                            || kt_tyle_bhxh != decimal.Parse(r["mbhxh_hs"].ToString().Trim())
                            || kt_tyle_bhyt != decimal.Parse(r["mbhyt_hs"].ToString().Trim())
                            || kt_tyle_bhtn != decimal.Parse(r["mbhtn_hs"].ToString().Trim())
                            || kt_ngaynghi != r["ngaynghi"].ToString().Trim()
                            || kt_trathe != r["trathe"].ToString().Trim())
                        {
                            nrow = ds_ts_mau03a.Tables["Table0"].NewRow();
                            string ghichu = "";
                            if (kt_heso_luong < decimal.Parse(r["hs_lc"].ToString().Trim()))
                            {
                                ghichu = "HSL: " + r["hs_lc"].ToString().Trim();
                                kt_heso_luong = decimal.Parse(r["hs_lc"].ToString().Trim());
                            }
                            if (kt_hscv != decimal.Parse(r["hs_cv"].ToString().Trim()))
                            {
                                kt_hscv = decimal.Parse(r["hs_cv"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng HSCV"; // hệ số chức vụ
                            }
                            if (kt_hsvk != decimal.Parse(r["hs_vk"].ToString().Trim()))
                            {
                                kt_hsvk = decimal.Parse(r["hs_vk"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng HSVK"; // hệ số vượt khung
                            }
                            if (kt_hstn != decimal.Parse(r["hs_tn"].ToString().Trim()))
                            {
                                kt_hstn = decimal.Parse(r["hs_tn"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng HSTN"; // hệ số thâm niên nghề
                            }
                            if (kt_hskv != decimal.Parse(r["hs_kv"].ToString().Trim()))
                            {
                                kt_hskv = decimal.Parse(r["hs_kv"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng HSKV"; // hệ số khu vực
                            }
                            if (kt_tyle_bhxh != decimal.Parse(r["mbhxh_hs"].ToString().Trim()))
                            {
                                kt_tyle_bhxh = decimal.Parse(r["mbhxh_hs"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng BHXH";
                            }
                            if (kt_tyle_bhyt != decimal.Parse(r["mbhyt_hs"].ToString().Trim()))
                            {
                                kt_tyle_bhyt = decimal.Parse(r["mbhyt_hs"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng BHYT";
                            }
                            if (kt_tyle_bhtn != decimal.Parse(r["mbhtn_hs"].ToString().Trim()))
                            {
                                kt_tyle_bhtn = decimal.Parse(r["mbhtn_hs"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng BHTN";
                            }
                            if (kt_ngaynghi != r["ngaynghi"].ToString().Trim())
                            {
                                kt_ngaynghi = r["ngaynghi"].ToString().Trim();
                                ghichu = "Thôi việc";
                            }
                            if (kt_trathe != r["trathe"].ToString().Trim())
                            {
                                kt_trathe = r["trathe"].ToString().Trim();
                                ghichu = "Trả thẻ BHYT";
                            }
                            nrow = ds_ts_mau03a.Tables["Table0"].NewRow();
                            //nrow["manv"] = r["manv"].ToString();
                            //nrow["hoten"] = r["hoten"].ToString();
                            //nrow["so_bhxh"] = r["so_bhxh"].ToString();
                            //nrow["ngaysinh"] = r["ngaysinh"].ToString();
                            nrow["hslc_cu"] = r["hslc_cu"].ToString();
                            nrow["hscv_cu"] = r["hscv_cu"].ToString();
                            nrow["hsvk_cu"] = r["hsvk_cu"].ToString();
                            nrow["hstn_cu"] = r["hstn_cu"].ToString();
                            nrow["hskv_cu"] = r["hskv_cu"].ToString();
                            nrow["hs_lc"] = r["hs_lc"].ToString();
                            nrow["hs_cv"] = r["hs_cv"].ToString();
                            nrow["hs_vk"] = r["hs_vk"].ToString();
                            nrow["hs_tn"] = r["hs_tn"].ToString();
                            nrow["hs_kv"] = r["hs_kv"].ToString();
                            nrow["thang_bhxh"] = r["thang_bhxh"].ToString();
                            nrow["nam_bhxh"] = r["nam_bhxh"].ToString();
                            nrow["thangnam_bhxh"] = r["thangnam_bhxh"].ToString();
                            nrow["dong_den_thang_nam"] = r["dong_den_thang_nam"].ToString();
                            nrow["den_thang"] = r["den_thang"].ToString();
                            nrow["den_nam"] = r["den_nam"].ToString();
                            nrow["tyle_bosung"] = r["tyle_bosung"].ToString();
                            nrow["trathe"] = r["trathe"].ToString();
                            nrow["ghichu"] = r["ghichu"].ToString();
                            nrow["sothang"] = r["sothang"].ToString();
                            nrow["mltt"] = r["mltt"].ToString();
                            nrow["sbhxh_hs"] = r["sbhxh_hs"].ToString();
                            nrow["sbhyt_hs"] = r["sbhyt_hs"].ToString();
                            nrow["sbhtn_hs"] = r["sbhtn_hs"].ToString();
                            nrow["mbhxh_hs"] = r["mbhxh_hs"].ToString();
                            nrow["mbhyt_hs"] = r["mbhyt_hs"].ToString();
                            nrow["mbhtn_hs"] = r["mbhtn_hs"].ToString();
                            nrow["bhxh"] = r["bhxh"].ToString();
                            nrow["bhyt"] = r["bhyt"].ToString();
                            nrow["bhtn"] = r["bhtn"].ToString();
                            nrow["thangnam_bacluong"] = r["thangnam_bacluong"].ToString();
                            nrow["thangnam_hesobh"] = r["thangnam_hesobh"].ToString();
                            nrow["thangnam_nangbac"] = r["thangnam_nangbac"].ToString();
                            nrow["loaiphucap"] = r["loaiphucap"].ToString();
                            nrow["ngaynghi"] = r["ngaynghi"].ToString();
                            nrow["ghichu"] = ghichu;

                            ds_ts_mau03a.Tables["Table0"].Rows.Add(nrow);
                            dong += 1;
                           
                        }
                        else
                        {
                            nrow = ds_ts_mau03a.Tables["Table0"].NewRow();
                            nrow["manv"] = r["manv"].ToString();
                            nrow["hoten"] = r["hoten"].ToString();
                            nrow["so_bhxh"] = r["so_bhxh"].ToString();
                            nrow["ngaysinh"] = r["ngaysinh"].ToString();
                            nrow["hslc_cu"] = r["hslc_cu"].ToString();
                            nrow["hscv_cu"] = r["hscv_cu"].ToString();
                            nrow["hsvk_cu"] = r["hsvk_cu"].ToString();
                            nrow["hstn_cu"] = r["hstn_cu"].ToString();
                            nrow["hskv_cu"] = r["hskv_cu"].ToString();
                            nrow["hs_lc"] = r["hs_lc"].ToString();
                            nrow["hs_cv"] = r["hs_cv"].ToString();
                            nrow["hs_vk"] = r["hs_vk"].ToString();
                            nrow["hs_tn"] = r["hs_tn"].ToString();
                            nrow["hs_kv"] = r["hs_kv"].ToString();
                            nrow["thang_bhxh"] = r["thang_bhxh"].ToString();
                            nrow["nam_bhxh"] = r["nam_bhxh"].ToString();
                            nrow["thangnam_bhxh"] = r["thangnam_bhxh"].ToString();
                            nrow["dong_den_thang_nam"] = r["dong_den_thang_nam"].ToString();
                            nrow["den_thang"] = r["den_thang"].ToString();
                            nrow["den_nam"] = r["den_nam"].ToString();
                            nrow["tyle_bosung"] = r["tyle_bosung"].ToString();
                            nrow["trathe"] = r["trathe"].ToString();
                            nrow["ghichu"] = r["ghichu"].ToString();
                            nrow["sothang"] = r["sothang"].ToString();
                            nrow["mltt"] = r["mltt"].ToString();
                            nrow["sbhxh_hs"] = r["sbhxh_hs"].ToString();
                            nrow["sbhyt_hs"] = r["sbhyt_hs"].ToString();
                            nrow["sbhtn_hs"] = r["sbhtn_hs"].ToString();
                            nrow["mbhxh_hs"] = r["mbhxh_hs"].ToString();
                            nrow["mbhyt_hs"] = r["mbhyt_hs"].ToString();
                            nrow["mbhtn_hs"] = r["mbhtn_hs"].ToString();
                            nrow["bhxh"] = r["bhxh"].ToString();
                            nrow["bhyt"] = r["bhyt"].ToString();
                            nrow["bhtn"] = r["bhtn"].ToString();
                            nrow["thangnam_bacluong"] = r["thangnam_bacluong"].ToString();
                            nrow["thangnam_hesobh"] = r["thangnam_hesobh"].ToString();
                            nrow["thangnam_nangbac"] = r["thangnam_nangbac"].ToString();
                            nrow["loaiphucap"] = r["loaiphucap"].ToString();
                            nrow["ngaynghi"] = r["ngaynghi"].ToString();
                            nrow["ghichu"] = "HSBL:" + kt_heso_luong.ToString();
                            ds_ts_mau03a.Tables["Table0"].Rows.Add(nrow);
                        }
                    }
                }
            }
            ds_ts_mau03a.WriteXml("..\\xml\\h_mau03a_bhxh_bhyt.xml", XmlWriteMode.WriteSchema);
            return ds_ts_mau03a;
           
       }
        private void btnchon_Click(object sender, EventArgs e)
        {
            //ds.AcceptChanges();
            tonghop();
            ReportName = "h_mau03a_bhxh_bhyt.rpt";
            msg = lan.Change_language_MessageText("DANH SÁCH ĐIỀU CHỈNH MỨC ĐÓNG BHXH, BHYT TỪ NGÀY ")+tungay +lan.Change_language_MessageText(" ĐẾN NGÀY ")+denngay;
            frmReport f = new frmReport(mDB, ds_ts_mau03a, msg, ReportName, user_dn);
            switch (checkxem.Checked)
            {
                case true:
                    f.Show();
                    if (checkupdate.Checked)
                        Update_bhxh(ds);
                    break;
                case false:
                    if (checkin.Checked)
                    {
                        f.PreviewReport();
                        f.butIn_Click(null, null);
                    }
                    if (checkupdate.Checked)
                        Update_bhxh(ds);
                    break;
            }
        }
        private void cmbquy_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }
        public void getngay()
        {
            tungay = haison1.tungay;
            denngay = haison1.denngay;
            s_mmyy = denngay.Substring(3, 2) + denngay.Substring(8, 2);
        }

        private void btnketthuc_Click(object sender, EventArgs e)
        {
            this.Close();
        }    
        public void Update_bhxh(DataSet dsbh)
        {
            if (DateTime.Now.Date >= Convert.ToDateTime(tungay))
            {
                //string quy = cmbquy.Text.ToString().PadLeft(2, '0');
                //string nam = txtnam.Text.Trim();
                foreach (DataRow dr in dsbh.Tables[0].Rows)
                {
                    mDB.update_bhxh(dr["manv"].ToString(), dr["hslc_cu"].ToString(), decimal.Parse(dr["hscv_cu"].ToString()), decimal.Parse(dr["hsvk_cu"].ToString()), decimal.Parse(dr["hstn_cu"].ToString()), decimal.Parse(dr["hskv_cu"].ToString()), decimal.Parse(dr["hs_lc"].ToString()), decimal.Parse(dr["hs_cv"].ToString()), decimal.Parse(dr["hs_vk"].ToString()), decimal.Parse(dr["hs_tn"].ToString()), decimal.Parse(dr["hs_kv"].ToString()), dr["tuthang"].ToString(), dr["denthang"].ToString(), decimal.Parse(dr["tyle"].ToString()),bool.Parse(dr["trathe"].ToString()), dr["ghichu"].ToString(), tungay + " Đến ngày "+denngay, nam, DateTime.Now.Date.ToString("dd/MM/yyyy"));
                }
            }
            else
            {
                MessageBox.Show(lan.Change_language_MessageText("Thời điểm cập nhật dữ liệu không hợp lệ!"));
                //cmbquy.Focus();
            }
        }     

        private void cmbhoten_SelectedIndexChanged(object sender, EventArgs e)
        {
            txtmanv.Text = cmbhoten.SelectedValue.ToString();
        }

        private void checkxem_CheckedChanged(object sender, EventArgs e)
        {
            if (!checkupdate.Checked)
            {
                if (checkxem.Checked)
                    checkin.Checked = false;
                else
                    checkin.Checked = true;
            }
        }

        private void checkin_CheckedChanged(object sender, EventArgs e)
        {
            if (!checkupdate.Checked)
            {
                if (checkin.Checked)
                    checkxem.Checked = false;
                else
                    checkxem.Checked = true;
            }
        }      

        
        public DataSet tao_dataset_excel()
        {
            DataTable dt_exp = new DataTable("Table0");
            DataTable dt_ts_exp = new DataTable("Table1");

            dt_exp.Columns.Add("stt", typeof(string));
            dt_exp.Columns.Add("hoten", typeof(string));
            dt_exp.Columns.Add("sobhxh", typeof(string));
            dt_exp.Columns.Add("ngaysinh", typeof(string));
            dt_exp.Columns.Add("hslc_cu", typeof(decimal));
            dt_exp.Columns.Add("hscv_cu", typeof(decimal));
            dt_exp.Columns.Add("hsvk_cu", typeof(decimal));
            dt_exp.Columns.Add("hstn_cu", typeof(decimal));
            dt_exp.Columns.Add("hskv_cu", typeof(decimal));
            dt_exp.Columns.Add("hs_lc", typeof(decimal));
            dt_exp.Columns.Add("hs_cv", typeof(decimal));
            dt_exp.Columns.Add("hs_vk", typeof(decimal));
            dt_exp.Columns.Add("hs_tn", typeof(decimal));
            dt_exp.Columns.Add("hs_kv", typeof(decimal));
            dt_exp.Columns.Add("tuthang", typeof(string));
            dt_exp.Columns.Add("denthang", typeof(string));
            dt_exp.Columns.Add("tyle", typeof(string));
            dt_exp.Columns.Add("trathe", typeof(string));
            dt_exp.Columns.Add("ghichu", typeof(string));
           
            //
            dt_ts_exp.Columns.Add("ts_ld_bhxh_tang", typeof(decimal));
            dt_ts_exp.Columns.Add("ts_ld_bhxh_giam", typeof(decimal));
            dt_ts_exp.Columns.Add("ts_ld_bhyt_tang", typeof(decimal));
            dt_ts_exp.Columns.Add("ts_ld_bhyt_giam", typeof(decimal));
            dt_ts_exp.Columns.Add("ts_ld_bhtn_tang", typeof(decimal));
            dt_ts_exp.Columns.Add("ts_ld_bhtn_giam", typeof(decimal));
            dt_ts_exp.Columns.Add("quyluong_bhxh_tang", typeof(decimal));
            dt_ts_exp.Columns.Add("quyluong_bhxh_giam", typeof(decimal));
            dt_ts_exp.Columns.Add("quyluong_bhyt_tang", typeof(decimal));
            dt_ts_exp.Columns.Add("quyluong_bhyt_giam", typeof(decimal));
            dt_ts_exp.Columns.Add("quyluong_bhtn_tang", typeof(decimal));
            dt_ts_exp.Columns.Add("quyluong_bhtn_giam", typeof(decimal));
            dt_ts_exp.Columns.Add("phaidong_bhxh_tang", typeof(decimal));
            dt_ts_exp.Columns.Add("phaidong_bhxh_giam", typeof(decimal));
            dt_ts_exp.Columns.Add("phaidong_bhyt_tang", typeof(decimal));
            dt_ts_exp.Columns.Add("phaidong_bhyt_giam", typeof(decimal));
            dt_ts_exp.Columns.Add("phaidong_bhtn_tang", typeof(decimal));
            dt_ts_exp.Columns.Add("phaidong_bhtn_giam", typeof(decimal));
            dt_ts_exp.Columns.Add("dieuchinh_bhxh_tang", typeof(decimal));
            dt_ts_exp.Columns.Add("dieuchinh_bhxh_giam", typeof(decimal));
            dt_ts_exp.Columns.Add("dieuchinh_bhyt_tang", typeof(decimal));
            dt_ts_exp.Columns.Add("dieuchinh_bhyt_giam", typeof(decimal));
            dt_ts_exp.Columns.Add("dieuchinh_bhtn_tang", typeof(decimal));
            dt_ts_exp.Columns.Add("dieuchinh_bhtn_giam", typeof(decimal));

            DataSet ds_tmp = new DataSet();
            ds_tmp.Tables.Add(dt_exp);
            ds_tmp.Tables.Add(dt_ts_exp);
            return ds_tmp;
        }
        public DataSet exp_exel(DataSet dse)
        {
            DataSet dse1 = new DataSet();
            dse1.Tables.Add();
            dse1.Tables[0].Columns.Add("stt", typeof(string));
            dse1.Tables[0].Columns.Add("hoten", typeof(string));
            dse1.Tables[0].Columns.Add("sobhxh", typeof(string));
            dse1.Tables[0].Columns.Add("ngaysinh", typeof(string));
            dse1.Tables[0].Columns.Add("hslc_cu", typeof(string));
            dse1.Tables[0].Columns.Add("hscv_cu", typeof(decimal));
            dse1.Tables[0].Columns.Add("hsvk_cu", typeof(decimal));
            dse1.Tables[0].Columns.Add("hstn_cu", typeof(decimal));
            dse1.Tables[0].Columns.Add("hskv_cu", typeof(decimal));
            dse1.Tables[0].Columns.Add("hs_lc", typeof(string));
            dse1.Tables[0].Columns.Add("hs_cv", typeof(decimal));
            dse1.Tables[0].Columns.Add("hs_vk", typeof(decimal));
            dse1.Tables[0].Columns.Add("hs_tn", typeof(decimal));
            dse1.Tables[0].Columns.Add("hs_kv", typeof(decimal));
            dse1.Tables[0].Columns.Add("tuthang", typeof(string));
            dse1.Tables[0].Columns.Add("denthang", typeof(string));
            dse1.Tables[0].Columns.Add("tyle", typeof(string));
            dse1.Tables[0].Columns.Add("trathe", typeof(string));
            dse1.Tables[0].Columns.Add("ghichu", typeof(string));

            dse1.Tables.Add();

            dse1.Tables[1].Columns.Add("ts_ld_bhxh_tang", typeof(decimal));
            dse1.Tables[1].Columns.Add("ts_ld_bhxh_giam", typeof(decimal));
            dse1.Tables[1].Columns.Add("ts_ld_bhyt_tang", typeof(decimal));
            dse1.Tables[1].Columns.Add("ts_ld_bhyt_giam", typeof(decimal));
            dse1.Tables[1].Columns.Add("ts_ld_bhtn_tang", typeof(decimal));
            dse1.Tables[1].Columns.Add("ts_ld_bhtn_giam", typeof(decimal));
            dse1.Tables[1].Columns.Add("quyluong_bhxh_tang", typeof(decimal));
            dse1.Tables[1].Columns.Add("quyluong_bhxh_giam", typeof(decimal));
            dse1.Tables[1].Columns.Add("quyluong_bhyt_tang", typeof(decimal));
            dse1.Tables[1].Columns.Add("quyluong_bhyt_giam", typeof(decimal));
            dse1.Tables[1].Columns.Add("quyluong_bhtn_tang", typeof(decimal));
            dse1.Tables[1].Columns.Add("quyluong_bhtn_giam", typeof(decimal));
            dse1.Tables[1].Columns.Add("phaidong_bhxh_tang", typeof(decimal));
            dse1.Tables[1].Columns.Add("phaidong_bhxh_giam", typeof(decimal));
            dse1.Tables[1].Columns.Add("phaidong_bhyt_tang", typeof(decimal));
            dse1.Tables[1].Columns.Add("phaidong_bhyt_giam", typeof(decimal));
            dse1.Tables[1].Columns.Add("phaidong_bhtn_tang", typeof(decimal));
            dse1.Tables[1].Columns.Add("phaidong_bhtn_giam", typeof(decimal));
            dse1.Tables[1].Columns.Add("dieuchinh_bhxh_tang", typeof(decimal));
            dse1.Tables[1].Columns.Add("dieuchinh_bhxh_giam", typeof(decimal));
            dse1.Tables[1].Columns.Add("dieuchinh_bhyt_tang", typeof(decimal));
            dse1.Tables[1].Columns.Add("dieuchinh_bhyt_giam", typeof(decimal));
            dse1.Tables[1].Columns.Add("dieuchinh_bhtn_tang", typeof(decimal));
            dse1.Tables[1].Columns.Add("dieuchinh_bhtn_giam", typeof(decimal));

            int stt = 0;
            DataRow nrow;
            foreach (DataRow r in dse.Tables["Table0"].Rows)
            {
                stt++;
                nrow = dse1.Tables[0].NewRow();
                nrow["stt"] = stt.ToString();
                nrow["hoten"] = r["hoten"].ToString();
                nrow["sobhxh"] = r["so_bhxh"].ToString();
                nrow["ngaysinh"] = r["ngaysinh"].ToString();
                nrow["hslc_cu"] = r["hslc_cu"].ToString();
                nrow["hscv_cu"] = r["hscv_cu"].ToString();
                nrow["hsvk_cu"] = r["hsvk_cu"].ToString();
                nrow["hstn_cu"] = r["hstn_cu"].ToString();
                nrow["hskv_cu"] = r["hskv_cu"].ToString();
                nrow["hs_lc"] = r["hs_lc"].ToString();
                nrow["hs_cv"] = r["hs_cv"].ToString();
                nrow["hs_vk"] = r["hs_vk"].ToString();
                nrow["hs_tn"] = r["hs_tn"].ToString();
                nrow["hs_kv"] = r["hs_kv"].ToString();
                nrow["tuthang"] = r["thangnam_bhxh"].ToString();
                nrow["denthang"] = r["dong_den_thang_nam"].ToString();
                nrow["tyle"] = r["tyle_bosung"].ToString();
                nrow["trathe"] = r["trathe"].ToString();
                nrow["ghichu"] = r["ghichu"].ToString();
                dse1.Tables[0].Rows.Add(nrow);
                
            }
            DataRow nrow1;
            foreach (DataRow r1 in dse.Tables["Table1"].Rows)
            {
                nrow1 = dse1.Tables[1].NewRow();
                nrow1["ts_ld_bhxh_tang"] = r1["ts_ld_bhxh_tang"].ToString();
                nrow1["ts_ld_bhxh_giam"] = r1["ts_ld_bhxh_giam"].ToString();
                nrow1["ts_ld_bhyt_tang"] = r1["ts_ld_bhyt_tang"].ToString();
                nrow1["ts_ld_bhyt_giam"] = r1["ts_ld_bhyt_giam"].ToString();
                nrow1["ts_ld_bhtn_tang"] = r1["ts_ld_bhtn_tang"].ToString();
                nrow1["ts_ld_bhtn_giam"] = r1["ts_ld_bhtn_giam"].ToString();
                nrow1["quyluong_bhxh_tang"] = r1["quyluong_bhxh_tang"].ToString();
                nrow1["quyluong_bhxh_giam"] = r1["quyluong_bhxh_giam"].ToString();
                nrow1["quyluong_bhyt_tang"] = r1["quyluong_bhyt_tang"].ToString();
                nrow1["quyluong_bhyt_giam"] = r1["quyluong_bhyt_giam"].ToString();
                nrow1["quyluong_bhtn_tang"] = r1["quyluong_bhtn_tang"].ToString();
                nrow1["quyluong_bhtn_giam"] = r1["quyluong_bhtn_giam"].ToString();
                nrow1["phaidong_bhxh_tang"] = r1["phaidong_bhxh_tang"].ToString();
                nrow1["phaidong_bhxh_giam"] = r1["phaidong_bhxh_giam"].ToString();
                nrow1["phaidong_bhyt_tang"] = r1["phaidong_bhyt_tang"].ToString();
                nrow1["phaidong_bhyt_giam"] = r1["phaidong_bhyt_giam"].ToString();
                nrow1["phaidong_bhtn_tang"] = r1["phaidong_bhtn_tang"].ToString();
                nrow1["phaidong_bhtn_giam"] = r1["phaidong_bhtn_giam"].ToString();
                nrow1["dieuchinh_bhxh_tang"] = r1["dieuchinh_bhxh_tang"].ToString();
                nrow1["dieuchinh_bhxh_giam"] = r1["dieuchinh_bhxh_giam"].ToString();
                nrow1["dieuchinh_bhyt_tang"] = r1["dieuchinh_bhyt_tang"].ToString();
                nrow1["dieuchinh_bhyt_giam"] = r1["dieuchinh_bhyt_giam"].ToString();
                nrow1["dieuchinh_bhtn_tang"] = r1["dieuchinh_bhtn_tang"].ToString();
                nrow1["dieuchinh_bhtn_giam"] = r1["dieuchinh_bhtn_giam"].ToString();
                dse1.Tables[1].Rows.Add(nrow1);
            }
            return dse1;
        }
        private DataSet tonghop_mau03a_excel()
        {
            kiemtra();
            ds_kq_mau03a_excel.Clear();
            ds_kq_mau03a_excel = tao_dataset(); 
            DataRow nrow, dr2;

            decimal sum_ps_bhxh_tang = 0, sum_ps_bhxh_giam = 0, sum_ps_bhyt_tang = 0, sum_ps_bhyt_giam = 0, sum_ps_bhtn_tang = 0, sum_ps_bhtn_giam = 0,
                                   sum_bs_bhxh_tang = 0, sum_bs_bhxh_giam = 0, sum_bs_bhyt_tang = 0, sum_bs_bhyt_giam = 0, sum_bs_bhtn_tang = 0, sum_bs_bhtn_giam = 0
                                   , ps_bhxh_tang = 0, ps_bhxh_giam = 0, ps_bhyt_tang = 0, ps_bhyt_giam = 0, ps_bhtn_tang = 0, ps_bhtn_giam = 0
                                   , bs_bhxh_tang = 0, bs_bhxh_giam = 0, bs_bhyt_tang = 0, bs_bhyt_giam = 0, bs_bhtn_tang = 0, bs_bhtn_giam = 0, he_mltt = 0;
            decimal sum_sld_bhxh_bhyt_tang = 0, sum_sld_bhxh_bhyt_giam = 0, sum_sld_bhtn_tang = 0, sum_sld_bhtn_giam = 0, sld_bhxh_bhyt_tang = 0, sld_bhxh_bhyt_giam = 0
                    , sld_bhtn_tang = 0, sld_bhtn_giam = 0;
            int dong = -1;
            // duyet de tinh tong
            if (ds_kq_mau03a.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds_kq_mau03a.Tables[0].Rows)
                {
                    sum_ps_bhxh_tang = decimal.Parse(r["ps_bhxh_tang"].ToString());
                    ps_bhxh_tang += sum_ps_bhxh_tang;
                    sum_ps_bhxh_giam = decimal.Parse(r["ps_bhxh_giam"].ToString());
                    ps_bhxh_giam += sum_ps_bhxh_giam;
                    sum_ps_bhyt_tang = decimal.Parse(r["ps_bhyt_tang"].ToString());
                    ps_bhyt_tang += sum_ps_bhyt_tang;
                    sum_ps_bhyt_giam = decimal.Parse(r["ps_bhyt_giam"].ToString());
                    ps_bhyt_giam += sum_ps_bhyt_giam;

                    sum_ps_bhtn_tang = decimal.Parse(r["ps_bhtn_tang"].ToString());
                    ps_bhtn_tang += sum_ps_bhtn_tang;
                    sum_ps_bhtn_giam = decimal.Parse(r["ps_bhtn_giam"].ToString());
                    ps_bhtn_giam += sum_ps_bhtn_giam;

                    sum_bs_bhxh_tang = decimal.Parse(r["bs_bhxh_tang"].ToString());
                    bs_bhxh_tang += sum_bs_bhxh_tang;
                    sum_bs_bhxh_giam = decimal.Parse(r["bs_bhxh_giam"].ToString());
                    bs_bhxh_giam += sum_bs_bhxh_giam;

                    sum_bs_bhyt_tang = decimal.Parse(r["bs_bhyt_tang"].ToString());
                    bs_bhyt_tang += sum_bs_bhyt_tang;
                    sum_bs_bhyt_giam = decimal.Parse(r["bs_bhyt_giam"].ToString());
                    bs_bhyt_giam += sum_bs_bhyt_giam;

                    sum_bs_bhtn_tang = decimal.Parse(r["bs_bhtn_tang"].ToString());
                    bs_bhtn_tang += sum_bs_bhtn_tang;
                    sum_bs_bhtn_giam = decimal.Parse(r["bs_bhtn_giam"].ToString());
                    bs_bhtn_giam += sum_bs_bhtn_giam;

                    sum_sld_bhxh_bhyt_tang = decimal.Parse(r["sld_bhxh_bhyt_tang"].ToString());
                    sld_bhxh_bhyt_tang += sum_sld_bhxh_bhyt_tang;
                    sum_sld_bhxh_bhyt_giam = decimal.Parse(r["sld_bhxh_bhyt_giam"].ToString());
                    sld_bhxh_bhyt_giam += sum_sld_bhxh_bhyt_giam;
                    sum_sld_bhtn_tang = decimal.Parse(r["sld_bhtn_tang"].ToString());
                    sld_bhtn_tang += sum_sld_bhtn_tang;
                    sum_sld_bhtn_giam = decimal.Parse(r["sld_bhtn_giam"].ToString());
                    sld_bhtn_giam += sum_sld_bhtn_giam;

                    he_mltt = decimal.Parse(r["mltt"].ToString());

                }
                //
                dr2 = ds_kq_mau03a_excel.Tables["Table1"].NewRow();

                dr2["tong_ps_bhxh_tang"] = ps_bhxh_tang.ToString();
                dr2["tong_ps_bhxh_giam"] = ps_bhxh_giam.ToString();
                dr2["tong_ps_bhyt_tang"] = ps_bhyt_tang.ToString();
                dr2["tong_ps_bhyt_giam"] = ps_bhyt_giam.ToString();
                dr2["tong_ps_bhtn_tang"] = ps_bhtn_tang.ToString();
                dr2["tong_ps_bhtn_giam"] = ps_bhtn_giam.ToString();
                //
                dr2["tong_bs_bhxh_tang"] = bs_bhxh_tang.ToString();
                dr2["tong_bs_bhxh_giam"] = bs_bhxh_giam.ToString();
                dr2["tong_bs_bhyt_tang"] = bs_bhyt_tang.ToString();
                dr2["tong_bs_bhyt_giam"] = bs_bhyt_giam.ToString();
                dr2["tong_bs_bhtn_tang"] = bs_bhtn_tang.ToString();
                dr2["tong_bs_bhtn_giam"] = bs_bhtn_giam.ToString();
                //
                dr2["ts_ld_bhxh_tang"] = sld_bhxh_bhyt_tang.ToString();
                dr2["ts_ld_bhxh_giam"] = sld_bhxh_bhyt_giam.ToString();
                dr2["ts_ld_bhyt_tang"] = sld_bhxh_bhyt_tang.ToString();
                dr2["ts_ld_bhyt_giam"] = sld_bhxh_bhyt_giam.ToString();
                dr2["ts_ld_bhtn_tang"] = sld_bhtn_tang.ToString();
                dr2["ts_ld_bhtn_giam"] = sld_bhtn_giam.ToString();
                //
                dr2["quyluong_bhxh_tang"] = decimal.Parse(dr2["tong_ps_bhxh_tang"].ToString()) * he_mltt;
                dr2["quyluong_bhxh_giam"] = decimal.Parse(dr2["tong_ps_bhxh_giam"].ToString()) * he_mltt;
                dr2["quyluong_bhyt_tang"] = decimal.Parse(dr2["tong_ps_bhyt_tang"].ToString()) * he_mltt;
                dr2["quyluong_bhyt_giam"] = decimal.Parse(dr2["tong_ps_bhyt_giam"].ToString()) * he_mltt;
                dr2["quyluong_bhtn_tang"] = decimal.Parse(dr2["tong_ps_bhtn_tang"].ToString()) * he_mltt;
                dr2["quyluong_bhtn_giam"] = decimal.Parse(dr2["tong_ps_bhtn_giam"].ToString()) * he_mltt;
                //
                dr2["phaidong_bhxh_tang"] = decimal.Parse(dr2["quyluong_bhxh_tang"].ToString()) * Convert.ToDecimal(0.2); // 20%
                dr2["phaidong_bhxh_giam"] = decimal.Parse(dr2["quyluong_bhxh_giam"].ToString()) * Convert.ToDecimal(0.2); // 20%
                dr2["phaidong_bhyt_tang"] = decimal.Parse(dr2["quyluong_bhyt_tang"].ToString()) * Convert.ToDecimal(0.03); // 3%
                dr2["phaidong_bhyt_giam"] = decimal.Parse(dr2["quyluong_bhyt_giam"].ToString()) * Convert.ToDecimal(0.03); // 3%
                dr2["phaidong_bhtn_tang"] = decimal.Parse(dr2["quyluong_bhtn_tang"].ToString()) * Convert.ToDecimal(0.02); // 2%
                dr2["phaidong_bhtn_giam"] = decimal.Parse(dr2["quyluong_bhtn_giam"].ToString()) * Convert.ToDecimal(0.02); // 2%
                //
                dr2["dieuchinh_bhxh_tang"] = dr2["tong_bs_bhxh_tang"].ToString();
                dr2["dieuchinh_bhxh_giam"] = dr2["tong_bs_bhxh_giam"].ToString();
                dr2["dieuchinh_bhyt_tang"] = dr2["tong_bs_bhyt_tang"].ToString();
                dr2["dieuchinh_bhyt_giam"] = dr2["tong_bs_bhyt_giam"].ToString();
                dr2["dieuchinh_bhtn_tang"] = dr2["tong_bs_bhtn_tang"].ToString();
                dr2["dieuchinh_bhtn_giam"] = dr2["tong_bs_bhtn_giam"].ToString();
                ds_kq_mau03a_excel.Tables["Table1"].Rows.Add(dr2);

            }
            if (ds_kq_mau03a.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds_mau03a.Tables[0].Rows)
                {
                    DataRow[] dr1 = ds_kq_mau03a.Tables[0].Select("manv= '" + dr["manv"].ToString() + "'", "mltt asc");
                    string str = dr1[0]["manv"].ToString();
                    decimal kt_heso_luong = 0, kt_hscv = 0, kt_hsvk = 0, kt_hstn = 0, kt_hskv = 0, kt_tyle_bhxh = 0, kt_tyle_bhyt = 0, kt_tyle_bhtn = 0;
                    string kt_ngaynghi = "", kt_trathe = "";
                    if (dr1.Length > 0)
                    {
                        kt_heso_luong = dr1[0]["hs_lc"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["hs_lc"].ToString().Trim()) : 0;
                        kt_hscv = dr1[0]["hs_cv"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["hs_cv"].ToString().Trim()) : 0;
                        kt_hsvk = dr1[0]["hs_vk"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["hs_vk"].ToString().Trim()) : 0;
                        kt_hstn = dr1[0]["hs_tn"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["hs_tn"].ToString().Trim()) : 0;
                        kt_hskv = dr1[0]["hs_kv"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["hs_kv"].ToString().Trim()) : 0;
                        kt_tyle_bhxh = dr1[0]["mbhxh_hs"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["mbhxh_hs"].ToString().Trim()) : 0;
                        kt_tyle_bhyt = dr1[0]["mbhyt_hs"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["mbhyt_hs"].ToString().Trim()) : 0;
                        kt_tyle_bhtn = dr1[0]["mbhtn_hs"].ToString().Trim() != "" ? decimal.Parse(dr1[0]["mbhtn_hs"].ToString().Trim()) : 0;
                        kt_ngaynghi = dr1[0]["ngaynghi"].ToString().Trim() != "" ? dr1[0]["ngaynghi"].ToString().Trim() : "";
                        kt_trathe = dr1[0]["trathe"].ToString().Trim() != "" ? dr1[0]["trathe"].ToString().Trim() : "";

                    }
                    foreach (DataRow r in dr1)
                    {
                        if (kt_heso_luong < decimal.Parse(r["hs_lc"].ToString().Trim())
                            || kt_hscv != decimal.Parse(r["hs_cv"].ToString().Trim())
                            || kt_hsvk != decimal.Parse(r["hs_vk"].ToString().Trim())
                            || kt_hstn != decimal.Parse(r["hs_tn"].ToString().Trim())
                            || kt_hskv != decimal.Parse(r["hs_kv"].ToString().Trim())
                            || kt_tyle_bhxh != decimal.Parse(r["mbhxh_hs"].ToString().Trim())
                            || kt_tyle_bhyt != decimal.Parse(r["mbhyt_hs"].ToString().Trim())
                            || kt_tyle_bhtn != decimal.Parse(r["mbhtn_hs"].ToString().Trim())
                            || kt_ngaynghi != r["ngaynghi"].ToString().Trim()
                            || kt_trathe != r["trathe"].ToString().Trim())
                        {
                            nrow = ds_kq_mau03a_excel.Tables["Table0"].NewRow();
                            string ghichu = "";
                            if (kt_heso_luong < decimal.Parse(r["hs_lc"].ToString().Trim()))
                            {
                                ghichu = "HSL: " + r["hs_lc"].ToString().Trim();
                                kt_heso_luong = decimal.Parse(r["hs_lc"].ToString().Trim());
                            }
                            if (kt_hscv != decimal.Parse(r["hs_cv"].ToString().Trim()))
                            {
                                kt_hscv = decimal.Parse(r["hs_cv"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng HSCV"; // hệ số chức vụ
                            }
                            if (kt_hsvk != decimal.Parse(r["hs_vk"].ToString().Trim()))
                            {
                                kt_hsvk = decimal.Parse(r["hs_vk"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng HSVK"; // hệ số vượt khung
                            }
                            if (kt_hstn != decimal.Parse(r["hs_tn"].ToString().Trim()))
                            {
                                kt_hstn = decimal.Parse(r["hs_tn"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng HSTN"; // hệ số thâm niên nghề
                            }
                            if (kt_hskv != decimal.Parse(r["hs_kv"].ToString().Trim()))
                            {
                                kt_hskv = decimal.Parse(r["hs_kv"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng HSKV"; // hệ số khu vực
                            }
                            if (kt_tyle_bhxh != decimal.Parse(r["mbhxh_hs"].ToString().Trim()))
                            {
                                kt_tyle_bhxh = decimal.Parse(r["mbhxh_hs"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng BHXH";
                            }
                            if (kt_tyle_bhyt != decimal.Parse(r["mbhyt_hs"].ToString().Trim()))
                            {
                                kt_tyle_bhyt = decimal.Parse(r["mbhyt_hs"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng BHYT";
                            }
                            if (kt_tyle_bhtn != decimal.Parse(r["mbhtn_hs"].ToString().Trim()))
                            {
                                kt_tyle_bhtn = decimal.Parse(r["mbhtn_hs"].ToString().Trim());
                                ghichu = "Thay đổi mức đóng BHTN";
                            }
                            if (kt_ngaynghi != r["ngaynghi"].ToString().Trim())
                            {
                                kt_ngaynghi = r["ngaynghi"].ToString().Trim();
                                ghichu = "Thôi việc";
                            }
                            if (kt_trathe != r["trathe"].ToString().Trim())
                            {
                                kt_trathe = r["trathe"].ToString().Trim();
                                ghichu = "Trả thẻ BHYT";
                            }
                            nrow = ds_kq_mau03a_excel.Tables["Table0"].NewRow();
                            
                            nrow["hslc_cu"] = r["hslc_cu"].ToString();
                            nrow["hscv_cu"] = r["hscv_cu"].ToString();
                            nrow["hsvk_cu"] = r["hsvk_cu"].ToString();
                            nrow["hstn_cu"] = r["hstn_cu"].ToString();
                            nrow["hskv_cu"] = r["hskv_cu"].ToString();
                            nrow["hs_lc"] = r["hs_lc"].ToString();
                            nrow["hs_cv"] = r["hs_cv"].ToString();
                            nrow["hs_vk"] = r["hs_vk"].ToString();
                            nrow["hs_tn"] = r["hs_tn"].ToString();
                            nrow["hs_kv"] = r["hs_kv"].ToString();
                            nrow["thang_bhxh"] = r["thang_bhxh"].ToString();
                            nrow["nam_bhxh"] = r["nam_bhxh"].ToString();
                            nrow["thangnam_bhxh"] = r["thangnam_bhxh"].ToString();
                            nrow["dong_den_thang_nam"] = r["dong_den_thang_nam"].ToString();
                            nrow["den_thang"] = r["den_thang"].ToString();
                            nrow["den_nam"] = r["den_nam"].ToString();
                            nrow["tyle_bosung"] = r["tyle_bosung"].ToString();
                            nrow["trathe"] = r["trathe"].ToString();
                            nrow["ghichu"] = r["ghichu"].ToString();
                            nrow["sothang"] = r["sothang"].ToString();
                            nrow["mltt"] = r["mltt"].ToString();
                            nrow["sbhxh_hs"] = r["sbhxh_hs"].ToString();
                            nrow["sbhyt_hs"] = r["sbhyt_hs"].ToString();
                            nrow["sbhtn_hs"] = r["sbhtn_hs"].ToString();
                            nrow["mbhxh_hs"] = r["mbhxh_hs"].ToString();
                            nrow["mbhyt_hs"] = r["mbhyt_hs"].ToString();
                            nrow["mbhtn_hs"] = r["mbhtn_hs"].ToString();
                            nrow["bhxh"] = r["bhxh"].ToString();
                            nrow["bhyt"] = r["bhyt"].ToString();
                            nrow["bhtn"] = r["bhtn"].ToString();
                            nrow["thangnam_bacluong"] = r["thangnam_bacluong"].ToString();
                            nrow["thangnam_hesobh"] = r["thangnam_hesobh"].ToString();
                            nrow["thangnam_nangbac"] = r["thangnam_nangbac"].ToString();
                            nrow["loaiphucap"] = r["loaiphucap"].ToString();
                            nrow["ngaynghi"] = r["ngaynghi"].ToString();
                            nrow["ghichu"] = ghichu;

                            ds_kq_mau03a_excel.Tables["Table0"].Rows.Add(nrow);
                            dong += 1;

                        }
                        else
                        {
                            nrow = ds_kq_mau03a_excel.Tables["Table0"].NewRow();
                            nrow["manv"] = r["manv"].ToString();
                            nrow["hoten"] = r["hoten"].ToString();
                            nrow["so_bhxh"] = r["so_bhxh"].ToString();
                            nrow["ngaysinh"] = r["ngaysinh"].ToString();
                            nrow["hslc_cu"] = r["hslc_cu"].ToString();
                            nrow["hscv_cu"] = r["hscv_cu"].ToString();
                            nrow["hsvk_cu"] = r["hsvk_cu"].ToString();
                            nrow["hstn_cu"] = r["hstn_cu"].ToString();
                            nrow["hskv_cu"] = r["hskv_cu"].ToString();
                            nrow["hs_lc"] = r["hs_lc"].ToString();
                            nrow["hs_cv"] = r["hs_cv"].ToString();
                            nrow["hs_vk"] = r["hs_vk"].ToString();
                            nrow["hs_tn"] = r["hs_tn"].ToString();
                            nrow["hs_kv"] = r["hs_kv"].ToString();
                            nrow["thang_bhxh"] = r["thang_bhxh"].ToString();
                            nrow["nam_bhxh"] = r["nam_bhxh"].ToString();
                            nrow["thangnam_bhxh"] = r["thangnam_bhxh"].ToString();
                            nrow["dong_den_thang_nam"] = r["dong_den_thang_nam"].ToString();
                            nrow["den_thang"] = r["den_thang"].ToString();
                            nrow["den_nam"] = r["den_nam"].ToString();
                            nrow["tyle_bosung"] = r["tyle_bosung"].ToString();
                            nrow["trathe"] = r["trathe"].ToString();
                            nrow["ghichu"] = r["ghichu"].ToString();
                            nrow["sothang"] = r["sothang"].ToString();
                            nrow["mltt"] = r["mltt"].ToString();
                            nrow["sbhxh_hs"] = r["sbhxh_hs"].ToString();
                            nrow["sbhyt_hs"] = r["sbhyt_hs"].ToString();
                            nrow["sbhtn_hs"] = r["sbhtn_hs"].ToString();
                            nrow["mbhxh_hs"] = r["mbhxh_hs"].ToString();
                            nrow["mbhyt_hs"] = r["mbhyt_hs"].ToString();
                            nrow["mbhtn_hs"] = r["mbhtn_hs"].ToString();
                            nrow["bhxh"] = r["bhxh"].ToString();
                            nrow["bhyt"] = r["bhyt"].ToString();
                            nrow["bhtn"] = r["bhtn"].ToString();
                            nrow["thangnam_bacluong"] = r["thangnam_bacluong"].ToString();
                            nrow["thangnam_hesobh"] = r["thangnam_hesobh"].ToString();
                            nrow["thangnam_nangbac"] = r["thangnam_nangbac"].ToString();
                            nrow["loaiphucap"] = r["loaiphucap"].ToString();
                            nrow["ngaynghi"] = r["ngaynghi"].ToString();
                            nrow["ghichu"] = "HSBL:" + kt_heso_luong.ToString();
                            ds_kq_mau03a_excel.Tables["Table0"].Rows.Add(nrow);
                        }
                    }
                }
            }
            return ds_kq_mau03a_excel;

        }
        private void print(int prn)
        {
            DataSet ds_excel_mau03a = new DataSet();
            ds_excel_mau03a = exp_exel(tonghop_mau03a_excel());
            if (ds_excel_mau03a.Tables[0].Rows.Count > 0)
            {
                switch (prn)
                {
                    case 1: exp.exp_dsdc_laodong_bhxh_bhyt(ds_excel_mau03a, "Bao cao danh sach dieu chinh lao dong tham gia BHXH,BHYT,BHTN", title, lan.Change_language_MessageText("TỪ NGÀY ") + tungay + lan.Change_language_MessageText(" ĐẾN NGÀY ") + denngay, s_mmyy);
                        break;
                }
            }
            else
            {
                MessageBox.Show(lan.Change_language_MessageText(" Chưa có dữ liệu"), lan.Change_language_MessageText("Thông báo"), MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
       
        private void haison1_MouseEnter(object sender, EventArgs e)
        {
            //kiemtra();
            //load_dcbhxh();
            //tonghop();
        }

        private void haison1_Validated(object sender, EventArgs e)
        {
            //kiemtra();
            load_dcbhxh();
            //tonghop();
        }

        private void btn_excel_Click(object sender, EventArgs e)
        {
            print(1);
        }

	}
}
